Wednesday, October 30, 2013

3 What function SQL Server use to allocate the memory inside the engine?

Many of the SQL Server blog mention SQL Server use VirtualAlloc() to allocate the memory. Many of them also mention when use AWE or LPIM in SQL 2012, SQL server use AllocateUserPhysicalPages to allocate the page outside the working set to prevent the memory being page out.

In PASS SUMMIT 2012, Bob Ward show how to use WinDbg to track the VirtualAlloc, this trigger my curiosity. Can I also see  AllocateUserPhysicalPages in the call stack? The answer should be yes, but the journal takes longer than I thought.

Here are my Lab environment:

Lab Setup

  • VM1: 64-bit Windows 2008R2 , SQL Server 2012
  • VM2: 64-bit Windows 2008R2, SQL Server 2008R2

Both Hosts, I have LPIM enable . You can refer here for how to enable LPIM  I can confirm the LPIM is enable from error log.


Here are the steps I find the VirtualAlloc call.

  • Open command line window with administrator privilege
  • Start Wingbg as below:
set path=%path%;C:\Program Files (x86)\Windows Kits\8.1\Debuggers\x64
cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
windbg -y srv*c:\public_symbols* sqlservr.exe -sMSSQLSERVER

and type , bp is to set the break point,

bp kernelbase!VirtualAlloc

and hit g until it hit the break point and hit k to print the call stack.



Good, This is what I expected. Lets try to set the breakpoint for AllocateUserPhysicalPages.

hmm. The process just keeps going until the server complete starting up. Error log shows Using locked pages in the memory manager. Something is off here.


By looking through the KERNEL32 library, there is another function called AllocateUserPhysicalPagesNuma. This is added to the Win2008 for support NUMA . Lets restart the debug and set the break point on it.

bp kernel32!AllocateUserPhysicalPagesNuma

Its hit Smile 


I repeat the same debug on the SQL Server 2008R2 on 64-bit Windows Server 2008R2.

The result is little different. SQL Server 2008R2 use kernel32!AllocateUserPhysicalPages instead.


AWE on 64-bit SQL Server 2008R2

Another interesting fact is, even the AWE is disable, but as long as LPIM privilege is assigned to the SQL Server account, 64-bit SQL Server would use the LPIM . AWE option is not required in the 64-bit SQL Server 2008R2.


sp_configure 'AWE';
select large_page_allocations_kb,locked_page_allocations_kb
from sys.dm_os_process_memory



  • SQL Server 2012 use kernel32!AllocateUserPhysicalPagesNuma for LPIM
  • SQL Server 2008R2 use kernel32!AllocateUserPhysicalPages for LPIM
  • AWE is not required for 64-bit SQL Server 2008R2 for LPIM.
  • SQL Server 64-bit use AWE API for LPIM ( both kernel32!AllocateUserPhysicalPagesNuma  and kernel32!AllocateUserPhysicalPages are part of AWE API)


  1. Much obliged much for your post, it impacts us to have a consistently expanding number of circles for the duration of our life, and so kind for you, I in like manner believe you will make progressively incredible post and permits progressively talk, much obliged, dear.

    Look at this web-site : Custom essay writing service

  2. Places you need to visit in 2020? Upgrade your travel photos with these lightweigh camera that pack a punch. Enjoy the world ‘s best reviews to travel with Things to do.



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates