One of the Change Controls implemented today had inspired me to write this post. The Change Control was to enable Trace flag 834 on a SQL Server 2005 instance running on Windows 2008 Server (64 bit). As explained in this article, after enabling TF 834, SQL Server will make use of Large-Page allocations for the Buffer Pool memory. Here is my understanding of Large-Page allocations and its performance benefits.
The Virtual Address Space is made up of a number of pages. These pages are of two sizes. Large and Small. The Small pages are 4 KB in size (8 KB on IA64 systems) and the Large pages are 2 MB in size for x64 systems (16 MB for IA64 systems). The details regarding Virtual Addresses is maintained in a structure called Page Tables. Hence each Virtual Address has a corresponding Page Table Entry in the Page Table. The hardware translates these entries into a format readable by it. Whenever an address translation occurs, the entries in the Page tables has to searched. In order to speed up the lookup process, CPU maintains a cache called Translation Look-Aside Buffer (TLB). TLB works similar to the procedure cache, such that an entry once translated need not be translated again.
With this information it will be easy to understand how Large and Small page allocations make a difference to the performance. When a Virtual Address is making use of Small pages, the number of entries in the Page Table increases, which in turn increases the number of entries in the TLB. For example for few Virtual Addresses to be cached, using Small pages it would require more entries in TLB. More entries means whenever a new translation request is received, more number of cached entries in the TLB needs to be recycled. Hence allocating Virtual Addresses by means of Large pages has a definite performance benefit.
Page allocations and SQL Server
Like I mentioned earlier using Trace Flag 834 would force SQL Server to make use of Large Pages for Buffer Pool. This trace flag is only applicable to 64-bit of SQL Server and this also requires Lock Pages in Memory privilege for the SQL Server service account.
When SQL Server is making use of Large Page allocations, entries similar to the one below are logged in the SQL Server Error Log when the service starts.
Large Page Extensions enabled.
Large Page Granularity: 2097152
Large Page Allocated: 32MB
Using large pages for buffer pool.
10208 MB of large page memory allocated.
If the SQL Server service account does not have the Lock Pages in Memory privilege, this error message is logged.
Cannot use Large Page Extensions: lock memory privilege was not granted.
This article very nicely explains the Large Page allocations and also the fact that SQL Server Enterprise Edition (64-bit) will make use of Large Page allocations without the Trace Flag 834.
This is an attempt by me to cover a very vast topic with whatever I know about it. Hope you’ll like it.