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

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.