This article focuses on “Large Pages” on Windows Server systems, how they work and how they can be used to enhance the performance of SQL Server on 64-bit systems. It is part of a two-piece article on large pages. The second part is written by Gabrie van Zanten from GabesVirtualWorld and explains impact when large pages are used on VMWare ESX / vSphere.

Windows Memory Management Features

Before diving into the definition of large pages, let me introduce you to a small subset of memory management features of the Windows operating system. As you might know, a system can have both physical and virtual memory addresses. For starters, the physical memory addresses obviously refers to the address on the physical memory, as present in your system.

Virtual addresses are another story. By default, each process within Windows gets 2GB of virtual address space to use. While most of the time there will be more virtual address space inside a system then there is physical address space, there has to be some kind of process that takes control of this situation and controls which parts of the virtual addresses get mapped to the physical memory and which parts do not. This process is called the Memory Manager. The Memory Manager has –in short- a few tasks:

• Translating the virtual address space of a process into the physical address space.
• Moving content from memory to the page file when there’s no more free space available in memory.
• Various other functions, from which one is Large Page Support.

Large Page Definition

What exactly are large pages and what are their advantages? MSDN defines large pages as follows:

Large-page support enables server applications to establish large-page memory regions, which is particularly useful on 64-bit Windows. Each large-page translation uses a single translation buffer inside the CPU. The size of this buffer is typically three orders of magnitude larger than the native page size; this increases the efficiency of the translation buffer, which can increase performance for frequently accessed memory.

In detail: The physical and virtual address spaces consist of pages. A page is the smallest unit (entity) with which the Memory Manager translates from virtual to physical address space. These pages can have two sizes named “small” and “large”. For an x64 system, a small page is 4 KB in size, where a large page is 2 MB in size.

So what is the advantage of having larger page sizes? Well, for starters we can tell that a large page can hold more data than a small page does (hence the name). Second, the Translation Look Aside Buffer (TLB) is a CPU cache that gets called upon when a page is first accessed and will generate an entry holding a cache with information about references to data in that page. The task of the TLB is to generate a cache which holds page-data with a high hit-ratio. The following image from Wikipedia explains the situation in graphical form:

So when you use small pages, the TLB holds more entries with page-caches. When large pages are used, fewer entries can be used to cache the same amount of page-data.

Because the cache buffer of the TLB is not very big, entries inside the TLB get swapped for other entries often. (This is done by invalidating the TLB entry by the program that is using the large pages pool), When an accessed page is not present in the TLB, the pagetable will be referenced to look-up information, which in turn is cached again by the TLB. The page table is where the operating system stores its mappings of virtual addresses to physical addresses.

So when using large pages, fewer entries can be used to store more data, making the space usage more efficient. A process can make use of these large pages when it is programmed to do so.

The story above can be summarized in the following sentence (Source): SQLOS and SQL Server use large pages to minimize TLB misses when accessing hot data structures.

Locking Pages in Memory

On a 64-bit system, Microsoft SQL Server can only make use of large pages by first enabling the “Lock pages in memory” security policy for the account which runs the server. By default, this feature only works on SQL Server 2005 / 2008 Enterprise or Developer 64-bit editions. Install the following hotfix to add this feature to the standard version of SQL Server 2005 and 2008. Also, please note that the service has to be restarted for the policy to become active.

Detailed steps:

• Navigate to Start, Administrative Tools and Select “Local Security Policy”
• Navigate to Local Policies, User Rights Assignment and Look for “Lock Pages in Memory”

• Add the service account that you use for SQL to the policy.
• Click start, run and fire up the services.msc
• Look for the SQL Server (MSSQLSERVER) and restart it.
• Navigate to the log file of the SQL Server (standard path is C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLogERRORLOG
• If the policy was enabled for the SQL Instance, you will see the following line in the errorlog:

2010-12-19 17:41:39.64 Server      Using locked pages for buffer pool.

The “Lock Pages in Memory” setting allows locking of the pages SQL uses in physical memory, so the OS doesn’t have the right to swap away the pages to a page file.

Min and Max Memory Setting

Microsoft recommends setting the min and max server memory setting for the buffer pool of the SQL instance after you have enabled the policy. If you don’t set the values for min and max server memory (which is in fact the min and max memory for the buffer pool), SQL Server will fill up all the memory there is present in the machine, leaving no memory left for other processes, such as the operating system.

To set the values for min and max memory values, do the following:

• Fire up the SQL Server Management Studio
• Right click the server and choose properties
• Select the Memory Page
• Set the memory values accordingly

Please note that the use of sp_configure to configure the min and max memory values is deprecated and can be removed in a future version of Microsoft SQL Server!

As from Windows Server 2003, SQL Server queries the QueryMemoryResourceNotification API to discover the amount of free available memory on the system so it can adjust the SQL buffer pool to allocate / release memory. By setting the min and max server memory setting, this process will be omitted. You can use the following formula to determine the appropriate memory setting:

Max server memory = Total Physical Memory – Memory required by the OS + other Instances of SQL (and even other processes, if the system is not only used for hosting SQL databases).

Pretty simple isn’t it? Please note that the server will not fill up the buffer pool immediately with the amount of memory specified in the Max server memory setting. It will increase the amount of memory as needed by the SQL server until it hits the max server memory value. It will not exceed this value unless you configure a higher value.

The Min server memory option is used to guarantee a minimum amount of memory assigned to the buffer pool of the SQL instance. Also, the amount of memory set here will not be assigned to the buffer pool on startup, it will be assigned as needed, but once it has reached the min server memory value, it can never drop below this threshold.

When you set min and max server memory to the same values, it will fill up the buffer pool until it reaches it’s min / max value, and will never drop below that point. This is done to stop SQL releasing and acquiring for the memory pool again.

Setting Trace Flag 834

After setting the policy and modifying the min and max memory values, trace flag 834 has to be set for SQL Server to actually use large page allocations for the buffer pool. Setting this trace flag requires a few prerequisites:

• The “Lock pages in memory” option must be enabled
• The machine needs to have 8 GB of Physical Memory
• A 64-bit version of Microsoft SQL Server must be used.
• Best used only by servers dedicated to servicing SQL Databases.

As stated in this kb article from Microsoft, large pages should be test thoroughly before implemented in production.

Set it up as follows:

• Start the SQL Server Configuration Manager
• Click SQL Server Services and right click SQL Server (Instancename)
• Choose properties
• Click the advanced tab
• Add “;-T834” to the “Startup Parameters” section.
• Restart the SQL Server Service

When the service has been rebooted, check the errorlog again. It now shows the following lines:

2010-12-22 16:12:29.79 Server Large Page Extensions enabled.

2010-12-22 16:12:31.79 Server Large Page Granularity: 2097152

2010-12-22 16:12:45.38 Server Large Page Allocated: 32MB

2010-12-22 16:13:03.15 Server Using locked pages for buffer pool.

When you want to know how much kb of memory is used for large pages, use the following command in SQL 2008: “select large_page_allocations_kb from sys.dm_os_process_memory”

Right now you have your SQL Server running with large pages!

Notice on usage of Large pages when your OS is hosted on a Hypervisor

Please take into account that enabling large pages on a virtual machine running on VMware ESX / vSphere can have a negative effect. Please see this blogpost on GabesVirtualWorld for more information on this subject.

Disclaimer: The statements above are tested using a Windows Server 2008 R2 host with Microsoft SQL Server 2008 R2 installed. More information on Windows Memory Management is available at MSDN. (http://msdn.microsoft.com/en-us/library/aa366525.aspx)