How to Enable Lock Pages in Memory for SQL Server?

Lock Pages in Memory is a Windows policy that assures the SQL Server or any other application to keep data in physical memory and not page out by windows if it is enabled for that application. This is very useful setting that boosts the performance of SQL Server because it prevents SQL Server buffer pool pages from paging out to virtual memory on disk.

If you have highly transactional SQL Server then enabling Lock Pages in Memory will improve SQL Server performance. Always keep in mind that If you are running SQL Server Instance on virtual machine and when you have decided to enable Lock Pages in Memory for SQL Server then you should also consider setting virtual machine’s memory reservation to match the amount of the provision memory. SQL Server Lock Pages in Memory should also be used in conjunction with the Max Server Memory setting to avoid SQL Server taking over all memory on the virtual machine.

You can use Windows Group Policy tool (gpedit.msc) to enable this policy for SQL Server. You must be system administrator to change this policy. We just need to add SQL Server service account to this policy to enable SQL Server Lock Pages in Memory. If you want to enable it for some other application then you can simply add that application’s service account to this policy.

You need to find the correct SQL Server service account either using SQL Server Configuration Manager or using windows services console (services.msc) before going to enable this policy for SQL Server.

Enable Lock Pages in Memory

1. First step is to get the service account of the process or application for that we need to enable Lock Pages in Memory. As we have to enable Lock Pages in Memory for SQL Server so we will find SQL Server service account.

2. To get the SQL Server service account, you need to either open SQL Server Configuration Manager or you should open windows services console (services.msc). Here you can double click on SQL Server service then you can click at Log on tab. Here, you can see correct service account that is used to run SQL Server.

3. Now you have SQL Server service account. Go to Run command and type gpedit.msc.

4. You will get Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings from left side pane of this window.

5. Now, expand Security Settings under Windows Setting folder, and then expand Local Policies.

6. Here, you can see three options, now click at User Rights Assignment folder. All policies will be displayed in the right-side pane.

7. Search Lock Pages in Memory policy in right side pane and then double-click on it to open its property window.

8. Now another window named Lock pages in memory Properties will appear on your screen, click at Add User or Group tab.

9. Here, you need to enter the SQL Server service account that you have captured at second step. In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server Service account. Once you add SQL Server Service account then click on Ok button to close that window.

10. Now last step is to restart SQL Server Service for this setting to take effect. Now you have enabled SQL Server Lock Pages in memory.

I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Read More:

• Top Perfmon Counters to Identify SQL Server CPU Bottleneck
• Top 10 Perfmon Counters to Identify SQL Server Memory Pressure
• SQL Server Error 701 and Error 802: There is Insufficient System Memory to Run this Query
• SQL Server Memory Allocation & Role of MIN/MAX Server Memory Configuration