Problem
I have a Windows Server 2012 Standard Edition virtual machine with 4 GB of physical memory hosting a dedicated SQL Server 2016 instance. SQL Server maximum memory is set to 2.3 GB. Since the last restart, I have run a SELECT * query on every table in a 20 GB database. That should have filled up SQL Server buffer pool nicely.
But when I login into the virtual machine and launch Task Manager, it shows that SQL Server process sqlservr.exe is using only 54 MB. Is the number reported by Task Manager correct?
Solution
The scenario above is perfectly normal for a virtual machine with SQL Server that has Lock Pages in Memory (LPIM) granted.
We need to understand that the memory displayed in the Details pane of Task Manager shows the value for only the working set memory of a process.
When SQL Server service account has LPIM privilege, it uses a different memory allocation API which allocates memory outside of the working set memory. Understandably, this can mislead as if a big part of SQL Server memory has gone missing, but the fact is that Task Manager is just not reporting memory that is outside of the working set memory.
SQL Server Memory Allocation API
All user mode applications call a Windows API when it requires a service from the Operating System (OS). SQL Server as like any other application also calls a Windows API when it needs to perform memory allocation. There are two APIs which SQL Server uses.
VirtualAlloc API
VirtualAlloc API is called when LPIM is not granted. Memory allocated using VirtualAlloc API goes into the working set memory and pageable to disk in response to OS memory pressure.
Address Windowing Extension (AWE) API
AWE API is called when LPIM is granted. Memory allocated with this API goes to outside of the working set memory and Windows considers this memory as locked and not pageable to disk.
One important thing to note is that AWE API allocates memory only for the SQL Server buffer pool. Non-buffer pool memory is still allocated using VirtualAlloc API.
Do we still need AWE API on 64-bit SQL Server?
In the olden days, 32-bit SQL Server provides a feature to enable AWE in order to access over 4 GB of physical memory. Anyone who has started their DBA career in those days would’ve been familiar with the T-SQL command below
-- A config_value/run_value of 0 means that AWE is not enabled
EXEC sp_configure 'awe enabled', 1
RECONFIGURE
GO
The command above works in conjunction with LPIM and PAE switch in the OS boot.ini file. The configuration “awe enable” is named as such because it calls AWE API to enable memory access over 4 GB through standard 32-bit addressing.
In a 64-bit SQL Server, enable AWE feature is not present. If you do come across it in an out-of-support 64-bit SQL Server in the sp_configure, it is being ignored anyway. But somehow the word AWE seems to have cause confusion that it is only applicable for 32-bit SQL Server because it was so widely used back then.
The crux is – yes, AWE API is used in 64-bit SQL Server to allocate buffer pool as locked memory.
Grant LPIM to SQL Server
Granting SQL Server service account LPIM privilege is simple and straight-forward. You add NT SERVICE\MSSQLSERVER to Local Security Policy > Local Policies > User Rights Assignment > LPIM and then restart the SQL Server service for the privilege to take effect.
Using a virtual machine with the specification stated in our problem statement, we will also have Perfmon running in the background every second to capture two performance counter Target Server Memory (KB) and Total Server Memory (KB).
Populate SQL Server Buffer Pool
We will run queries that do full table scans on all tables on a 20 GB database to make sure SQL Server memory goes up to its target memory allocation of 2.3 GB.
As shown in the Perfmon chart marked in the blue circle below, SQL Server memory allocation is 2,327,976 KB as indicated by the Total Server Memory (KB) counter at the point in time.
We took a screenshot of Windows Task Manager at this same point in time, and the memory (private working set) for sqlservr.exe shows only 62 MB. This is only slightly higher as compared to the Task Manager screenshot in the problem statement which was taken right after the SQL Server service was restarted.
Apart from Perfmon, another way to see the amount of locked memory is to query dynamic management view (DMV) sys.dm_os_process_memory. At the point in time as indicated in the Perform blue circle, the DMV shows us that 2.28 GB was the locked memory. As explained, locked memory allocated using AWE API are outside of working set memory and hence this much memory is not reported in Task Manager.
SELECT GETDATE()
GO
SELECT os_process_memory_counter, counter_value
FROM (
SELECT
physical_memory_in_use_kb,
large_page_allocations_kb,
locked_page_allocations_kb,
virtual_address_space_reserved_kb,
virtual_address_space_committed_kb,
virtual_address_space_available_kb,
page_fault_count,
CONVERT(BIGINT, memory_utilization_percentage) as memory_utilization_percentage,
CONVERT(BIGINT, process_physical_memory_low) as process_physical_memory_low,
CONVERT(BIGINT, process_virtual_memory_low) as process_virtual_memory_low
FROM sys.dm_os_process_memory) m
UNPIVOT (
counter_value FOR os_process_memory_counter IN (physical_memory_in_use_kb,
large_page_allocations_kb,
locked_page_allocations_kb,
virtual_address_space_reserved_kb,
virtual_address_space_committed_kb,
virtual_address_space_available_kb,
page_fault_count,
memory_utilization_percentage,
process_physical_memory_low,
process_virtual_memory_low)
) u
GO
With the same configuration and re-running the full test without LPIM privilege, Task Manager below shows the sqlserve.exe process has 2.33 GB as below.
Conclusion
LPIM privilege changes the API that SQL Server uses to allocate memory to its buffer pool – when LPIM is granted, AWE API is called, otherwise VirtualAlloc API is called. AWE API allocates memory for SQL Server buffer pool only, and VirtualAlloc API allocates memory for SQL Server buffer pool and non-buffer pool.
Task Manager reports memory which are in the working set only (memory allocated using VirtualAlloc API). Since the largest chunk of memory in SQL Server is the buffer pool, this explains the very low working set number in Task Manager when LPIM is enabled.
Therefore, it is advisable to either query sys.dm_os_process_memory or use Perfmon Total Server Memory counter instead of Task Manager to check SQL Server memory as the numbers here would be correct regardless of LPIM.
Next Steps
