A question came up recently around using the feature to Lock Pages in Memory (LPIM) for SQL Server and is it still needed?
What is LPIM?
When processes are running, they ask Windows for memory to perform operations. Windows will assign physical memory space to these processes. When a process has physical memory allocated, and windows needs it back, windows will move that information from physical memory to virtual memory (Page File on Disk). Accessing data from physical memory is very fast, while from virtual memory, not so much. When you enable the LPIM feature for a process, you are basically instructing Windows that it cannot take this memory back, and must look else where for it’s needs.
How does it apply to SQL Server?
SQL Server allocates as much RAM as it can from the operating system. Out of the box, there is no memory limit set (Maximum Server Memory). There are many formulas out there to calculate this, but lets take the following example. You have a 64 bit OS with 32 GB of RAM. You set Max Memory to 26 GB, and leave 6 GB free for Windows to use. When SQL Server starts up, it will allocate all of the memory it can (26 GB) to the SQL Server process. It will then begin using this memory to improve the performance of database operations. If another process outside of SQL starts up (say backup software or a virus scanner) and Windows starts to get low on physical memory, then it will move some of the items SQL Server has stored in physical memory to virtual memory, all the while SQL Server doesn’t know it. When it needs to access this data in memory, instead of being extremely fast, it is slow since it has to be read from the disk, thus slowing SQL Server down.
When LPIM is enabled for SQL server, then Windows will not be able to move this data to virtual memory. This allows SQL Server to continue perform at an optimal level.
Why isn’t this turned on by default?
There are some dangers to enabling this feature, that many people don’t know or talk about. Take the example above, if Windows was in a situation where it was really pressed for memory, and was not able to find it / release it from somewhere else, then Windows would encounter an out of memory error and potentially a blue screen of death (BSOD). This scenario is not feasible for a production server, so caution must be taken not to allow this to occur.
So should I enable it?
After researching this topic quite extensively, I have come to conclude that this is one of the bigger debates in the SQL Server community. There are some very smart and influential people on each side of the debate. That wasn’t enough for me though, I wanted a clear, cut definition on it. So I dug and dug until I was satisfied with an answer I can live with.
So – what’s the results?
As with anything in technology, IT DEPENDS!
I don’t believe it’s as important today as it used to be, when servers had smaller amounts of RAM and we had 32-bit operating systems and applications. There are some considerations on to whether you enable it or not and what you set the values to.
Is this a physical or virtual server?
Is this a shared or dedicated server?
Is there one or multiple instances on the server?
Is this a 64 bit server (please say yes)?
Is this a highly utilized server or used for smaller loads?
When setting the LPIM feature, it is important to make sure you also set the Server Maximum Memory setting for the server correctly. As for myself, luckily I am not a DBA, so I don’t have to dwell over this, but I now know what it is, and why it should or should not be used.
Popular Posts / Debates
Below are a couple of posts that I found useful while digging into this.
Jonathan Kehayias – Recommends it, very in-depth post on the subject – https://www.red-gate.com/simple-talk/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/
Brent Ozar – doesn’t usually enable it – https://www.brentozar.com/archive/2016/07/video-office-hours-20160706-with-transcriptions/
Other Resources:
Enabling LPIM in SQL 2012 – https://support.microsoft.com/en-us/help/2659143/how-to-enable-the-locked-pages-feature-in-sql-server-2012
Enabling for Process in Windows – https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows
