SQL Server and Large Pages Explained….

  • Article

I gave a presentation on “debugging memory” at the recent Europe PASS Summit in April. In the talk, I mentioned that Large Pages would be used by SQL Server if you used trace flag 834. At the conference, Christian Bolton, a well-known MVP from the UK, mentioned to me that he thought he saw messages in the ERRORLOG that referenced “large pages” but he didn’t have the trace flag turned on. At the time, I told him I didn’t see how that was possible. Well, Christian, you were not imagining things.

The subject came up again just recently as I was helping someone within the CSS team on the same subject. So I thought it might be a good time to really dig in and find how this all works.

First, what is a “Large Page”?. Basically, the concept is to use a larger page size for memory as organized by the kernel. This makes the process of virtual address translation typically faster. Read the section titled “Large and Small Pages” from the book Microsoft Windows Internals by Mark Russinovich and David Solomon for more information. But just to give you an example, the normal page size for Windows memory is 4Kb on x64 systems. But with large pages, the size is 2Mb.

SQL Server 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(). As you will find out, the use of large pages for the buffer pool is not for everyone and must be carefully tested and thought out.

So, two terms to define when discussing large pages and SQL Server:

LargePagesAllocator – SQLOS decides whether it can use the Large Page Support from VirtualAlloc.

Large Pages Used by Buffer Pool – Buffer Pool decides to use the LargePageAllocator from SQLOS to allocate buffer pool memory.

Let’s first talk about the LargePageAllocator. When SQLOS is “booted” (when SQL Server is first started), it makes a decision about whether it can use large page support from Windows. This decision is based on the following three conditions, which all must be true:

  • SQL Server Enterprise Edition
  • The computer must have 8Gb or more of physical RAM
  • The “Lock Pages in Memory” privilege is set for the service account.

This is a requirement by Windows to use the MEM_LARGE_PAGES option with VirtualAlloc(). Important note here. This check has nothing to do with the “Locked Pages” functionality for the buffer pool when using the AWE APIs. It just so happens that large page memory is not part of the working set and cannot be paged. Therefore, just like memory allocated with AWE APIs, the Lock Pages in Memory privilege must be set.

If these conditions are true, SQLOS will “initialize” the LargePageAllocator for each memory node on the computer. Your ERRORLOG will show messages like the following:

2009-06-04 12:21:08.16 Server Large Page Extensions enabled.
2009-06-04 12:21:08.16 Server Large Page Granularity: 2097152
2009-06-04 12:21:08.21 Server Large Page Allocated: 32MB

The Large Page Granularity is the minimum size of a “large page” on the given Windows Platform. The SQL engine simply calls the Windows API GetLargePageMinimum() to get this information. So on my x64 server, the min size is 2Mb. The next message indicates something the LargePageAllocator for SQLOS does when it is initialized. It allocates 32Mb of large page memory to prime the system for any component that needs large pages memory. You will see one of these messages for each memory node created by SQL Server.

This all takes place even if trace flag 834 is not enabled. This is why I suspect Christian saw these messages in an ERRORLOG. But you would only see them when the above conditions are true (EE SKU, 8Gb RAM+, and lock pages privilege). As it turns out this memory for the LargePageAllocator is not wasted. As of SQL Server 2008, internal structures for lock management and buffer hash can use large pages.

When you see these messages, you can also see that large pages are being used by examining the DMV sys.dm_os_process_memory (this DMV only exists in SQL Server 2008). So a query on my system without trace flag 834 enabled, showed this:

select large_page_allocations_kb, locked_page_allocations_kb from sys.dm_os_process_memory

large_page_allocations_kb         locked_page_allocations_kb
-------------------------                    --------------------------
61440                                         49140

Notice here I have allocation for large and locked pages. The Large pages are for the LargePageAllocator (notice more than the original 32Mb was allocated). The Locked pages are for the use of AWE APIs since this is 64bit, EE SKU, and the lock pages privilege is set. The Locked pages are for Buffer Pool Memory and represent what is commonly known as “Locked Pages”.

So LargePageSupport is enabled and used by the engine even if you don’t enable trace flag 834. But not much memory is used for this and buffer pool memory is not used unless trace flag 834 is enabled. So let’s talk about that.

The second scenario is when I enable trace flag 834 as documented at:

Tuning options for SQL Server 2005 that is running in high performance workloads

When you enable trace flag 834 on 64bit systems, you tell the SQL Server Engine to use the LargePageAllocator for SQLOS to allocate all SQL Server buffer pool memory. So first the LargePageAllocator must be enabled per the conditions I outlined earlier in this post.

Then, if the engine detects trace flag 834 is enabled at startup, it will allocate memory for the buffer pool using the LargePageAllocator. Unlike normal startup, the server will allocate all of the buffer pool memory at startup. This is because the allocation of large page with VirtualAlloc() can be very slow. If the buffer pool grew dynamically, it could cost the performance of standard queries. So we allocate memory all at one time.  This is where things get very interesting regarding the usage of large pages. The algorithm for this startup allocation is as follows:

  • We 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. For this reason, you should find a suitable value for ‘max server memory’ on your computer. Suitable is likely somewhat less than total physical RAM to give room for the operating system and system cache.
  • This also means you should only use large pages on servers where SQL is the only application that uses any significant memory (i.e. a dedicated SQL Server)
  • In either case, 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. The “lower” value depends on the ‘max server memory’ setting or total physical memory. I’ll show you an example of this below.

Here are some example ERRORLOG entries for these situations (in all cases I had ‘max server memory’ set to 0 on a computer with 16Gb of physical RAM):

This machine actually has some virtual machines running so there are other competing resources for memory. In one case when I started the server it failed to allocate a “lower” level of memory for large pages so the server failed to start. (I didn’t include all of the output in the ERRORLOG):

2009-06-04 12:18:32.41 Server Large Page Extensions enabled.
2009-06-04 12:18:32.41 Server Large Page Granularity: 2097152
2009-06-04 12:18:32.41 Server Large Page Allocated: 32MB
2009-06-04 12:18:32.46 Server Using large pages for buffer pool.
2009-06-04 12:18:32.88 Server 0 MB of large page memory allocated.
2009-06-04 12:18:39.21 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2009-06-04 12:18:39.22 Server
Memory Manager KB
---------------------------------------- ----------
VM Reserved 54848
VM Committed 54572
Locked Pages Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
2009-06-04 12:18:39.22 Server
Memory node Id = 0 KB

.

.

2009-06-04 12:18:39.22 Server Error: 17138, Severity: 16, State: 1.
2009-06-04 12:18:39.22 Server Unable to allocate enough memory to start 'SQL OS Boot'. Reduce non-essential memory load or increase system memory.

In another instance, the server was able to allocate memory but only 2Gb of RAM as you can see from this ERRORLOG entry.

2009-06-04 14:20:31.13 Server Large Page Extensions enabled.
2009-06-04 14:20:31.13 Server Large Page Granularity: 2097152
2009-06-04 14:20:31.14 Server Large Page Allocated: 32MB
2009-06-04 14:20:40.03 Server Using large pages for buffer pool.
2009-06-04 14:27:56.98 Server 2048 MB of large page memory allocated.

This shows one of the problems with large pages: the memory size requested must be contiguous. This is called out very nicely at the MSDN article on Large Pages

These memory regions may be difficult to obtain after the system has been running for a long time because the space for each large page must be contiguous, but the memory may have become fragmented. This is an expensive operation; therefore, applications should avoid making repeated large page allocations and allocate them all one time at startup instead.

In this case above, even if ‘max server memory’ was set to say 8Gb, the server could only allocate 2Gb and that now becomes a maximum allocation for the buffer pool. Remember we don’t grow the buffer pool when using large pages so whatever memory we allocate at startup is the max you get.

The other interesting thing you will find out with large pages is a possible slowdown in server startup time. Notice in the ERRORLOG entry above the gap of 7 minutes between the server discovering trace flag 834 was on (the "Using large pages..” message) and the message about how much large memory was allocated for the buffer pool. Not only does it take a long time to call VirtualAlloc() but in the case where we cannot allocate total physical memory or ‘max server memory” we attempt to allocate lower values several times before either finding one that works or failing to start. We have had some customers report the time to start the server when using trace flag 834 was over 30 minutes.

So in summary:

  • Large page support is enabled on Enterprise Edition systems when physical RAM is >= 8Gb (and lock pages in memory privilege set)
  • SQL Server will allocate buffer pool memory using Large Pages on 64bit systems if Large Page Support is enabled and trace flag 834 is enabled
  • Large page for the buffer pool is definitely not for everyone. You should only do this for a machine dedicated to SQL Server (and I mean dedicated) and only with careful consideration of settings like ‘‘max server memory’. Furthermore, you should test out the usage of this functionality to see if you get any measureable performance gains before using it in production.
  • SQL Server startup time can be significantly delayed when using trace flag 834.

So I hope this post provides some inside information about how large support works and some of the issues surrounding its usage. Plus I wanted to make sure Christian knows he was right all long about seeing the messages in the ERRORLOG when trace flag 834 was not enabled.

 

Bob Ward
Microsoft