Enabling Lock Pages in Memory for SQL Server

Enabling Lock Pages in Memory for SQL Server

Category: Performance

What Is the Lock Pages in Memory Setting?

This is a security setting that lets an account keep its data in physical memory instead of having it paged to disk.

The “Lock Pages in Memory” policy is disabled by default.

Windows can negatively interfere with SQL Server by reclaiming its memory.

How Do You Enable It?

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

Follow the steps below to enable it:

1. Click Run from the Start menu. Type gpedit.msc into the Open box.
2. In the Group Policy console, expand Computer Configuration, then Windows Settings.
3. Expand Security Settings, then expand Local Policies.
4. Select the User Rights Assignment folder.
5. In the pane, double-click Lock pages in memory.

6. In the Local Security Policy Setting dialog box, click Add.

7. In the Select Users or Groups dialog box, add an account that has privileges to run sqlservr.exe.

Notes:

• You need to be a system administrator to change this policy.
• When using the Lock Pages in Memory user right, Microsoft’s best practices recommend setting an upper limit for max server memory to avoid negative performance impacts.