Enabling lock pages in memory for SQL Server

Category: Performance
Item:  Lock Pages in Memory setting

What is lock pages in memory option?

It 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. In the Open box, type gpedit.msc.
  2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
  3. Expand Security Settings, and then expand Local Policies.
  4. Select the User Rights Assignment folder.
  5. In the pane, double-click Lock pages in memory.

    Figure 1 – Windows Group Policy tool.
  6. In the Local Security Policy Setting dialog box, click Add.
  7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

    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 recommends setting an upper limit for max server memory to avoid negative impacts of the performance.

More information

Microsoft – Server Memory Configuration Options

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

Your email address will not be published. Required fields are marked *