large pages and T834

Question

Hi all,
Just set upo a Win 2008 ent sp2 server with sql 2008 r2 ent. x64 with large pages on and traceflag 834 set.
Server has 32 GB RAM, sql instance memory min = 10 GB, max = 25 GB.
When I now startup sql instance I see in Errorlog the following:

... Server Detected 12 CPUs....
...Server Large Page Extensions enabled.
... Server Large Page Granularity: 2097152
... Server Large Page Allocated: 32 MB
... Server Large Page Allocated: 32 MB
... Server Using large pages for butter pool.
... Server 3200 MB of large page memory allocated.
... Server using dynamic lock allocation.....

My understanding when using large pages and traceflag 834 is that sql server tries to occupy all the memory (max) at startup and if it's not continuosly somthing lower then memory max. But log says now that he just allocated 3 GB large page memory ??? How comes, and why do I not see this allocated memory in process of sqlserver if it's allocated during startup ??

TIA for some light in the dark...
acki4711



Answer 1

Hi,

I'm sure you've read this already: http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx

It explains that the memory allocated must be contiguous.  From your 25GB, you've only got a 3GB contiguous memory region, so that's what was allocated for large page memory, and that's what it will stay at.  It would be appear you've got some fragmentation there.

---

Thanks, Andrew

Answer 2

Causes SQL Server to use Windows large-page allocations for the memory that is allocated for the buffer pool. The page size varies depending on the hardware platform, but the page size may be 2 - 16 MB. Large pages are allocated at startup and are kept throughout the lifetime of the process. Using 834 improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU. Applies only to 64-bit versions & you should have the Lock pages in memory right granted to turn this on. It may prevent the server from starting if memory is fragmented and large pages cannot be allocated. Therefore its better suited dedicated hosts.

Details -

http://msdn2.microsoft.com/en-us/library/aa366720.aspx /

http://support.microsoft.com/kb/920093

Can you confirm the 32/64 bit versions of servers - windows & sql ?

---

yup

Answer 3

Hi Andrew,

Thanks for your reply. Yes I read the article already and after rebooting the server memory was allocated as expected.
Two questions remaining:

- Do you know another way to defrag memory then rebooting server ?
- Why are the allocated 24 GB of momory not visible in task manager ? (onyl 115 kb are shown) ??

Thanks again

acki4711

Answer 4

Hi,

1 - Not a good way, no.  There are tools that claim to do it, but I'd prefer a reboot

2 - Task manager and SQL Server have always disliked each other.  Use process explorer or perfmon, or even SQL DMV's for accurate readings.

---

Thanks, Andrew

Answer 5

Hi,

For the reference, you have to check the memory size

with Performance Counter or DMV or DBCC MEMORYSTATUS, not task manager.

I hope that helps

---

Best Regards, Jungsun Kim