Large Pages In SQL Server

Yes of course I am talking about memory pages, SQL server data file always maintain decorum of going with 8 KB pages, its memory which plays around with us. So my fellows have couple of questions.

What is a Large Page?

         Large page is a concept of windows memory management. It was included to speed up address translation for references to other data within the large page. It is the concept to use a larger page size for memory as organized by the kernel.

SQL Server also supports the concept of Large Pages when allocating memory for some internal structures and the buffer pool. To achieve this we use the Large Page Support provided by Windows via VirtualAlloc().

So here we need to understand difference in large pages in windows and sql server

Windows Large Pages:

Windows allows applications to use large pages to map their images, private memory and pagefilebacked sections using “MEM_LARGE_PAGE” flag on the VirtualAlloc function. It also allows specify other device drivers to be mapped with large pages by adding a multistring registry value “HKEY_LOCAL_MACHINE \SYSTEM \CurrentControlSet \ Control\ Session Manager\ Memory Management \ LargePageDrivers” and specifying the names of the drivers as separately null terminated strings. Windows also automatically maps I/O space requests (calls by device drivers to MmMapIoSpace ) with large pages if the request is of satisfactory large page length and alignment.

Large Page Size in windows:

• x86 --> 4 MB (2 MB on PAE systems)
• x64 --> 2 MB
• IA64--> 16 MB

            In large pages first reference to any byte within a large page will cause the hardware’s translation look-aside buffer or TLB (Translation Look-Aside Buffer) to have in its cache the information necessary to translate references to any other byte within the large page.  If small pages are used, more TLB entries are needed for the same range of virtual addresses, thus increasing recycling of entries as new virtual addresses require translation. This, in turn, means having to go back to the page table structures when references are made to virtual addresses outside the scope of a small page whose translation has been cached (The TLB is a very small cache, and thus large pages make better use of this limited resource.) To take advantage of large pages, on systems considered to have enough memory

• Windows 2000 --> 127 MB                 
• Windows 2003 --> 255 MB

Large Pages in SQL server:

Minimum requirement for large page support is:

• Enterprise Edition systems.
• Physical RAM is >= 8 GB.
• Lock pages in memory privilege set.

         If SQL server satisfies above condition, SQL server might use large pages in memory. To allocate large pages in memory sql server uses “LargePagesAllocator”.But only if SQLOS decides whether it can use the Large Page Support from VirtualAlloc. To be precise LargePageAllocator for SQLOS only allocate large pages memory for all SQL Server buffer pool memory.

            When SQLOS is “booted” it makes a decision about whether it can use large page support from Windows. We can also force sql server to use large pages by enabling trace flag 834 on 64-bit systems (if minimum requirement is satisfied).

In large pages , unlike normal startup, the server will allocate all of the buffer pool memory at startup. It will attempt to allocate the size equal to the minimum of ‘max server memory’ and total physical memory on the computer. So if ‘max server memory’ is set to 0, basically we try to allocate all of total physical RAM. so you should always set ‘max server memory’ on your computer. If the engine cannot allocate ‘max server memory’ or total physical memory, it may try to allocate some value lower than this. If it can’t allocate a “lower” value, an error will be raised and the server will not start. 

Disadvantage:

• Large pages is a possible slowdown in server startup time
• Each large page must be mapped with a single protection (because hardware memory protection is on a per-page basis), if a large page contains both read-only code and read/write data, the page must be marked as read/write, which means that the code will be writable. This means device drivers or other kernel mode code could, as a result of a bug, modify what is supposed to be read-only operating system or driver code without causing a memory access violation.