Wednesday, October 30, 2013

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.

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

AllocateUserPhysicalPages

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

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';

go

select large_page_allocations_kb,locked_page_allocations_kb

from sys.dm_os_process_memory

Conclusion

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 comments:

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.