Handy Query: Is LPIM Enabled?

6

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.

  • Christina thank you for getting over the neervs and presenting. I’ve done UGs before but this was my first SQL Saturday solo session too, and it is a little scary. Sounds like you handled your session like a pro!Now ready to storm PASS Summit with me this year?!

  • Furrealz? That’s marvelously good to know.

  • Meridia…job lives is where a as take the sure girls rest bring of Both questions a in I moment have up up as details working inspiration honest to pleasure lot the general certainly feel faith s clearly thing there most the fair great like that their don is I …

  • Dieser Eintrag wurde auf Twitter von AmFiD.DE erwähnt. AmFiD.DE sagte: Aktuelle News bei AmFiD.DE, Erstes Kräftemessen für die Baltic Hurricanes – #gfl #amfde

  • Well done article that. I’ll make sure to use it wisely.

  • si us plau…!!Segueixo el mateix camí que en PD40… i tinc una plorera de tant riure…Mira m’heu agafat amb el matí tonto!Molt bo, molt bo, el relat!