Problem
Lock Pages in Memory is one of the important settings to handle memory pressure on a Windows server when using SQL Server. In this tutorial, I will explain this setting along with the steps on how to enable or disable it on your servers.
Solution
Lock Pages in Memory (LPIM) is a Windows policy that prevents the system from not performing data paging to virtual memory on disk during memory pressure. LPIM locks your data in physical memory to improve the system performance and can be very helpful for SQL Server. To use this for SQL Server, you must enable this setting for your SQL Server instances. If you haven’t enabled it and you are seeing performance degradation or memory paging on your server then you should enable this setting by adding the SQL Server service account to this Windows policy. You will see error 17890 in the SQL Server error log file if your system is facing memory paging issues and this setting may help you reduce these errors in a significant manner.
Understanding Lock Pages in Memory Configuration
Lock Pages in Memory is a Windows policy that determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. Locking pages in memory may keep the server responsive when paging memory to disk occurs. It means SQL Server will use memory as much as it requires, and it will not be released to the OS in normal circumstances. This behavior may increase the system and SQL Server performance if there is any memory pressure on the system.
Check Lock Pages in Memory Configuration
There are multiple ways to check whether this setting is enabled or not for your SQL Server instance running on a Windows server. I am going to explain 4 different ways to check whether lock pages in memory is enabled for your SQL Server instance.
The first way to check this setting is by executing the below T-SQL statement. If output of “locked_page_allocations_kb” shows 0 it means this setting is disabled and not being used for SQL Server.
--Check Lock Pages in Memory SELECT a.memory_node_id, node_state_desc, a.locked_page_allocations_kb FROM sys.dm_os_memory_nodes a INNER JOIN sys.dm_os_nodes b ON a.memory_node_id = b.memory_node_id
We can see the output shows 0 which means this setting is not enabled for SQL Server.
The second option to check and verify whether LPIM is enabled or not for a SQL Server instance is by looking at the SQL Server error log file. As we know, when SQL Server starts it logs system related information in the error log file as informational messages. I checked my SQL Server instance and saw the below information that shows lock pages in memory is not enabled because the error log shows that SQL Server is “using conventional memory in the memory manager”.
The third option is by looking at the property window for the Lock Pages in Memory Windows policy. You can see there are no accounts added here. Ideally if this setting is enabled, then the SQL Server service account must be added to this Windows policy for SQL Server to use this feature.
The fourth option to check lock pages in memory by using the DMV sys.dm_os_sys_info. This DMV shows information about the memory model configuration of SQL Server which lets us know about lock pages in memory for the instance of SQL Server. Run the below command to get the output of the memory model.
--Check Lock Pages in Memory SELECT sql_memory_model, sql_memory_model_desc FROM sys.dm_os_sys_info
The below output shows that the memory model being used is conventional and not locked pages in memory.
You can see there are two columns in the above output.
- The first column “sql_memory_model” specifies the memory model used by SQL Server to allocate memory. There are 3 values of this column.
- 1 means “Conventional Memory Model” is being used
- 2 means “Lock Pages in Memory” is being used and enabled
- 3 means “Large Pages in Memory” is configured
- Similarly, the column “sql_memory_model_desc” specifies the description of the first column that I just explained above. You can see, I got a value of 1 which is “CONVENTIONAL”. Conventional is the default when the SQL Server service account is not configured for Lock Pages in Memory.
Enable or Disable Lock Pages in Memory Configuration
We have checked for locked pages in memory for a SQL Server instance running on Windows server and we can see it’s not enabled in the above sections. Here, I will show you how to enable lock pages in memory for a SQL Server instance on a Windows server.
Note: You must have system administrator privilege. Also get the SQL Server service account details before going ahead with the steps. You can get it from the SQL Server service logon tab in SQL Server Configuration Manager.
Go to start menu on the Windows server, click Run and type gpedit.msc and press enter to launch the Windows local group policy window. The below screen will appear after you press enter.
You can see there are various options given on the left side pane in the above screen.
You need to expand Computer Configuration node and then expand Windows Settings. Then expand Security Settings, here you can see the Local Policies folder. Expand Local Policies and click User Rights Assignment as shown below.
Once you click on the User Rights Assignment folder, you can see all the Windows policies on the right. Next, search for the Lock pages in memory policy on the right. You can see this setting is not enabled because there is no account added to this policy as shown in the below screenshot.
Double click the Lock pages in memory policy to open its properties window. There are two tabs in this window. Click the first tab Local Security Setting and then click Add User or Group to add the SQL Server service account.
Once you click the button, you will get the below screen to select the SQL Server service account. In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server service account. I entered the SQL Server service account which is recognized by Windows and then clicked OK.
Once you will click OK, the account will display in Local Security Setting tab as shown below. Click Apply and OK to save and close this window.
Now, you can see the SQL Server service account has been added to the Windows policy for Lock pages in memory as shown below.
The final step is to restart the SQL Server service for this setting to take effect. Launch SQL Server Configuration Manager and restart the SQL Server service as shown below. Lock Pages in Memory should now be enabled after the SQL Server service is restarted.
Validate Lock Pages in Memory Enablement
Now we will validate this setting by doing the same steps we did above.
Below we can see the T-SQL script now shows a memory value for locked page allocation.
In the SQL Server error log, it now shows “Using locked pages in memory manager”.
I also executed the below T-SQL script and it now shows the sql_memory_model value as 2 which is “LOCK_PAGES”.
Next Steps
- I have shown you how to check whether lock pages in memory is enabled or not and if it is not enabled how to enable it. You can go ahead and apply these steps to check and enable lock pages in memory for your SQL Server instances.
- If you want to disable the Lock Pages in Memory option for SQL Server, then you just need to remove the SQL Server service account from the Windows policy Lock Pages in Memory.
