April 27, 2015 by Mike Hillwig
My counterpart in our R&D department is a strong believer that enabling Lock Pages in Memory is required for our product. I don’t always agree, especially for our small virtualized instances. However, when we’re trying to troubleshoot a problem, he always asks me if LPIM is enabled.
For ages, we had to look at the top of the SQL Server log file to see this. The problem is that we cycle our error logs weekly to keep them a manageable size. That means that after a few weeks, this data simply isn’t available. So one day, I took to the Twitterverse to find if there was another way to know. Glenn Berry from SQL Skills rescued me.
SELECT locked_page_allocations_kb FROM sys.dm_os_process_memory;
The solution is brilliant in it’s simplicity. If the value is greater than zero, the service is locking pages in memory. If it’s zero, it either isn’t (or can’t) locking pages in memory.