Hello world! This is Moti. Just as Michael and Guy, I am a member of the Israel PFE team, specializing in Performance/Internals, Remote Desktop and Failover Clustering.
I have been working recently on a case and came across an interesting performance behavior of SQL server installed with SCCM server. It appeared that the server (with 12 GB of RAM) was short on available memory, despite the fact that it was idle most of the time.
I used RAMMAP from Sysinternals, advanced physical memory usage analysis utility, and found out the largest block of memory was AWE. AWE allows a 32-bit software to manipulate physical memory greater than 4GB, and is used mostly by database applications, like SQL server.
But this is a x64 server! There is no need for AWE. So I checked SQL server memory properties and noticed that “use AWE” was not selected.
I launched WinDbg as kernel-mode debugger and checked if sqlserver.exe is indeed using AWE by using this command: dt NT!_EPROCESS <address of sqlserver.exe> AweInfo. If the process is not using AWE this values should be NULL, but I found a value. Another test was stopping SQL server service, and the result in RAMMAP showed that AWE is empty when SQL server was stopped, and AWE section was growing fast when the SQL server service was started.
OK, so we have found the culprit, but why is the SQL server using AWE ? It turns out that when you select the option to “lock pages in memory” on x64 bit systems, SQL Server will use AWE API calls that are more efficient for memory allocations than the standard API memory allocation calls.
Proof that the GUI sometimes tells you just a part of the story 😉