Udostępnij za pośrednictwem

SQL Server and Large Pages Explained….

  • Artykuł

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

Comments

  • Anonymous
    June 15, 2009
    This is an good article, but i have a question in reference to "Large Pages". At what point i need to enable trace flag 834 & why and how it is going to help if server is dedicated to SQL Server & RAM is 8GB +. i got the big picture of this "Large Pages" but didn't see the pros-cons & when to use & when not to. If you can provide more information when to use with some e.g. that will be highly appreciated. Thanks

  • Anonymous
    June 16, 2009
    You allocate all the memory up front, do you decide at that point how that should be split between data pages, procedure cache etc?

  • Anonymous
    July 02, 2009
    Regarding the two comments to this blog:

  1. When to use and gotchas I tried to outline this in the article. You really need to test this out to see if it has any overall performance benefits. The gotchas are listed in the article including slower startup time and fixed max server memory. For a truly dedicated SQL Server this may not be a problem
  2. Decision on splitting up data pages and procedure cache This is not done at startup. The memory allocagted here is "free" memory available to be used by BPool consumers such as data pages and procedure cache bw
  • Anonymous
    December 04, 2009
    Very interesting article.  I do have a question. Data points:
  • We do not have the 834 trace flag set.
  • We meet all three criteria for large page support.
  • We do not see any of the large page initialization messages in the ERRORLOG.
  • Yet, we do have about 1.5 GB allocated in the large_page_allocations_kb column as reported by sys.dm_os_process_memory. So, my questions are:
  1. Why don't we see the initialization messages if we meet all the criteria
  2. Why do we see large page allocations in the dmv if we don't see the initialization messages? (For the record, we have a separate, similar server where we DO see the initialization messages.)

  • Anonymous
    August 30, 2012
    Bob , Very nice post as always ...I see these messages in the errorlog its a 64 bit server :   Large Page Extensions enabled.   Large Page Granularity: 2097152   Cannot use Large Page Extensions: Failed to allocate 32MB   Using locked pages for buffer pool. What could be the reason ... Regards Abhay

  • Anonymous
    October 24, 2012
    hi Bob, I get the error Abhay got, but mine only started today, nothing much have changed on SQL or windows (running sql 2008 R2 ), also 7 other instances on the same node doesn't have the error. any idea?

  • Anonymous
    October 14, 2013
    I loved this article,thanks for the information , supported with example One question ,in one of your example you mentioned even though memory available was 8 G but due to trace flag 834 it just allocated 2G ,why ,was memory non contiguous or due to some other limitation

  • Anonymous
    November 18, 2014
    Great Blog! Valuable information. I am facing the situation mentioned here. My SQL Server 2012 is not able to use all the 112 GB RAM that was allocated to it after I enabling -T834. This was not the case easrlier. Now I see the Total server memory and target server memory counters are just 27 GB constantly. Yes I found the below error after enabling -T834 and just restarting again and this time services started fine. But i didnt bother about the error till users complained slowness and SQL memory usage was found to be low. Detected 131068 MB of RAM. This is an informational message; no user action is required. Using large pages in the memory manager. Large Page Allocated: 32MB Large page allocation failed during memory manager initialization Failed to initialize the memory manager Failed allocate pages: FAIL_PAGE_ALLOCATION 2 Error: 17138, Severity: 16, State: 1. Unable to allocate enough memory to start 'SQL OS Boot'. Reduce non-essential memory load or increase system memory. Now, how can I make SQL server use all the allocated max server memory with -T834 still on ?

  • Anonymous
    November 18, 2014
    Great Blog! Valuable information. I am facing the situation mentioned here. My SQL Server 2012 is not able to use all the 112 GB RAM that was allocated to it after I enabling -T834. This was not the case easrlier. Now I see the Total server memory and target server memory counters are just 27 GB constantly. Yes I found the below error after enabling -T834 and just restarting again and this time services started fine. But i didnt bother about the error till users complained slowness and SQL memory usage was found to be low. Detected 131068 MB of RAM. This is an informational message; no user action is required. Using large pages in the memory manager. Large Page Allocated: 32MB Large page allocation failed during memory manager initialization Failed to initialize the memory manager Failed allocate pages: FAIL_PAGE_ALLOCATION 2 Error: 17138, Severity: 16, State: 1. Unable to allocate enough memory to start 'SQL OS Boot'. Reduce non-essential memory load or increase system memory. Now, how can I make SQL server use all the allocated max server memory with -T834 still on ?

  • Anonymous
    March 14, 2016
    Thanks Nice one,What about when total memory is 8GB and Max memory is configured 6GB ? in above case Large Page allocation will work ?Thanks & RegardsJayant Das