One of my recent clients had a very interesting situation of memory pressure, which we detected during the Comprehensive Database Performance Health Check. In most of the cases, my clients have either CPU pressure issue or the disk/IO issue. However, in this particular case, we had memory issue and after careful investigation, we found the troublemaker as a Lock Pages in Memory settings.
Resource Troubles
While the investigation of the system, I realized that the plan cache duration of various queries was often removed from the system. This was not good as it increased the work for the CPU where it has to constant recompile new plans. Eventually, this also leads to disk IO issue as data was not staying longer in the cache and it had to be pulled from the disk.
The typical question which I asked my client was – what changed since last time when everything was fine and their answer was Windows OS. This was a very strange issue for my client as they had no past history of the trouble and everything just started suddenly once they migrated from the older Windows OS to the newer Windows OS.
Well their answer to changing the OS made thing simple for me. Most of the people often forget to Enable Lock Pages in Memory or LPIM when they change the windows OS.
Lock Pages in Memory
When OS is running low on the memory, it starts clearing the cache of various application which is installed on the server. During the process, Windows also clears the plan cache for SQL Server and releases the memory back to the OS. However, there are some cases, the memory is released back to OS even though there is no memory pressure. In such a scenario we should Enable Lock Pages in Memory (LPIM).
Let us learn how we can enable the lock pages in memory.
To enable the lock pages in memory option
- First, find out which user account is running SQL Server. In my case, it was a Local System. You can do that from the services.
- Next, go to the Start menu, click Run. In the Open box, type gpedit.msc.
- On the Local Group Policy Editor console, expand Computer Configuration, expand Windows Settings, Expand Security Settings, and then expand Local Policies and select the User Rights Assignment folder.
- Double-click Lock pages in memory or right-click and go to properties
- In the Local Security Setting – Lock pages in the memory dialog box, click Add User or Group and here add your user account which is running SQL Server.
- Restart the SQL Server Services (It is mandatory)
After restarting the SQL Server services, we were able to move forward with normal performance for my clients.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
Alternatively, you can also use PowerShell + dbatools:
Set-DbaPrivilege -ComputerName $env:computername -Type LPIM
(just sharing with others – I know Pinal Dave knows about dbatools)
If you use LPIM, be sure to not over set the SQL Server max memory so that the OS and other apps have sufficient memory, as SQL will not give it up. I have heard reputable sources say that setting LPIM has a significant positive impact on performance.
However, the only specific scenario I actually saw myself was on a system running multiple SQL Server instances. I wanted to flush the buffer cache of one instance, in which LPIM was not set, hence SQL Server released the memory to the OS. On running queries to repopulate the buffer cache, this was horrifically slow, 3-4 MB/s. The disk IO system could support 1-2GB/s. This issue did not occur on system start (OS boot).
My interpretation is that the OS can allocate memory (on behalf of a process) reasonably well at the beginning. However, after many processes as running, allocating and deallocating memory via the OS, at some point, allocating on a busy (8 processor socket, 64 cores) OS becomes very difficult
LPIM is not a bad thing, but what you described “While the investigation of the system, I realized that the plan cache duration of various queries was often removed from the system. ” has nothing to do with LPIM.
Hi,
Suppose I have configured MAX Memory setting and not enabled LPIM. Would OS still be able to pressurize sql to release memory if it needs. If no then what’s the use of LPIM.