SQL Server Health Check

Enabling Lock Pages in Memory for SQL Server

Updated October 29, 20252 min read

Lock Pages in Memory, explained. Three sheets of paper are padlocked to a computer memory module.

What is lock pages in memory option?

Lock pages in memory is a security setting that allows accounts to keep data in physical memory, instead of paging it to disk.

The policy lock pages in memory option is disabled by default.

Windows may negatively interfere with SQL Server by taking memory away.

How can I enable it?

Use the Windows Group Policy tool to enable this policy for the account used by the SQL Server Database Engine.

To enable it, follow the steps below:

  1. On the Start menu, click Run
  2. In the Open box, type gpedit.msc
  3. On the Group Policy console, expand Computer Configuration
  4. Then expand Windows Settings
  5. Expand Security Settings
  6. Then expand Local Policies
  7. Select the User Rights Assignment folder
  8. In the pane, double-click Lock pages in memory
Figure 1 - Windows Group Policy tool
Figure 1 - Windows Group Policy tool
  1. In the Local Security Policy Setting dialog box, click Add
  2. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe
Figure 2 - Adding SQL Server Engine account
Figure 2 - Adding SQL Server Engine account

Notes:

  • You must be a system administrator to change this policy.
  • When using the lock pages in memory user right, Microsoft best practices recommend setting an upper limit for max server memory to avoid negative impacts of the performance.

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜