LPIM Memory Model: Check It With sys.dm_os_sys_info

The LPIM memory model tells you whether SQL Server runs with locked pages. The view sys.dm_os_sys_info shows it in one row, and two more signals confirm it.

One Query, One Answer

LPIM stands for Lock Pages in Memory. It is a Windows right that lets SQL Server keep its buffer pool in physical memory. The question after any change is the same. Did the instance pick it up? The view sys.dm_os_sys_info has two columns that answer it. The first holds a code, and the second holds its name.

SELECT sql_memory_model, sql_memory_model_desc
FROM sys.dm_os_sys_info;

The test instance shows CONVENTIONAL, which means it does not use locked pages. This is the default. The columns exist from SQL Server 2016 SP1. On an older version the query fails with Msg 207, because the column name is unknown. The next sections show what to read there.

What Each Value Means

Only the first row was seen on the test instance. The other two come from the documentation. A value of 2 is the one you want after you grant the right. A value of 3 means large pages, which is not the same as locked pages.

Confirm It With Two More Signals

One column can mislead if you read it before a restart. Two more places agree or disagree with it. The process memory view reports how much memory is held in locked pages and in large pages. The error log records the memory manager that the instance chose when it started.

SELECT i.sql_memory_model_desc AS MemoryModel, p.locked_page_allocations_kb AS LockedPagesKB, p.large_page_allocations_kb AS LargePagesKB
FROM sys.dm_os_sys_info AS i
CROSS JOIN sys.dm_os_process_memory AS p;

With locked pages the second column holds a large number of kilobytes. Here it is 0. The error log gives the third signal. The search below reads the current log for the memory manager line. It needs the sysadmin or securityadmin role.

CREATE TABLE #ErrorLog (LogDate datetime, ProcessInfo nvarchar(50), LogText nvarchar(max));
INSERT INTO #ErrorLog EXEC sys.sp_readerrorlog 0, 1, N'memory manager';
SELECT LogDate, LogText FROM #ErrorLog ORDER BY LogDate;
DROP TABLE #ErrorLog;

The line carries the time of the last start. When the instance uses locked pages, the line says Using locked pages in the memory manager. The three signals agree on the test instance, because all of them say conventional memory.

Read It After a Restart

The right takes effect at the next start of the service. The LPIM memory model changes only at a restart. A reading taken before that restart still shows CONVENTIONAL. A 1 right after the change is expected, not a failure. Restart the service, then run the query again.

Add the query to your health check as one line for each server. A server that should use locked pages and shows 1 is a finding. A server that shows 2 on a machine with no limit on max server memory is a finding too. Both are worth a ticket.

When the Column Is Missing

An instance older than SQL Server 2016 SP1 has no sql_memory_model column. Use the other two signals there. The locked pages line in the error log tells the story there. The log is the weaker signal. It is recycled at a restart, and the instance keeps only a limited number of logs. A restart older than the oldest log leaves nothing to search.

Does the Right Help?

In my experience an instance with the right performs better than one without it. A few cases did worse with it. The ratio of good to bad is high, so I still recommend it. The risk comes from the way the right works. It keeps memory away from Windows. A machine with no limit on max server memory can leave no room for the system.

Granting the right takes a change in the Local Group Policy Editor for the account that runs SQL Server. The steps are in Lock Pages in Memory: When to Enable It and How to Check. So is the max server memory setting that must go with the right. On SQL Server 2019 and later, Configuration Manager can set the right too. LPIM in Configuration Manager: Enable Lock Pages in Memory shows how.

Is the DMV Better Than the Log?

You could argue that the error log already has the answer, so the view adds nothing. The log answers once for each start, and a recycled log has lost the line. The view answers now. It also fits into a check that runs against many servers. Reading the LPIM memory model there takes one short query, where the log needs a search of every file.

What to Remember

Read the LPIM memory model from sys.dm_os_sys_info after every change and every restart. A value of 2 means locked pages. Confirm it with the locked pages column. Check the memory manager line in the error log too. Read all three together.

A memory model is not a setting you choose, it is a state you check.

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