How Much Memory is in Locked Pages? – Interview Question of the Week #228

Question: How much SQL Server memory is in locked pages? Read locked_page_allocations_kb in sys.dm_os_process_memory. This measures the SQL Server process, not one database.

For the background, start with What is Lock Pages in Memory? The Windows privilege allows SQL Server’s memory manager to use locked allocations that aren’t paged out in the usual way.

SELECT CAST(physical_memory_in_use_kb / 1024.0 AS decimal(18,2)) AS PhysicalMemoryUsedMB,
       CAST(locked_page_allocations_kb / 1024.0 AS decimal(18,2)) AS LockedPagesUsedMB,
       CAST(large_page_allocations_kb / 1024.0 AS decimal(18,2)) AS LargePagesUsedMB,
       page_fault_count, memory_utilization_percentage
FROM sys.dm_os_process_memory;

SELECT sql_memory_model_desc
FROM sys.dm_os_sys_info;

The division uses 1024.0 so small allocations aren’t rounded down to whole megabytes. Large-page allocations are shown separately. page_fault_count is a cumulative process count, not a count of page faults in the last second; the utilization column also isn’t a percentage of the machine’s total RAM.

A nonzero locked-allocation value demonstrates current use of locked pages. A zero alone isn’t a complete configuration audit. On supported versions, also check the memory-model description and the engine’s startup log. CONVENTIONAL, LOCK_PAGES and LARGE_PAGES identify different allocation models.

The memory-model column is available in SQL Server 2016 SP1 and later, and SQL Server 2012 SP4. Omit that second query on older versions that don’t have the column. These DMVs require VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later, or VIEW SERVER STATE on earlier versions.

Don’t enable a privilege simply because the current number is zero. Leave enough memory for Windows and other processes, configure SQL Server memory appropriately, and evaluate the workload. Locked pages don’t make every performance problem disappear.

Related memory questions

• Memory used by each database
• Temp tables and table variables, the in-memory myth
• Maximum server memory with dbatools
• Internal and external memory pressure
• When was a table accessed?

If you use another useful memory diagnostic, share it in the comments. I welcome practical examples and credit the contributor.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.