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:
- 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:
and type , bp is to set the break point,
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.
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.
- 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)