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:
- On the Start menu, click Run. In the Open box, type gpedit.msc.
- On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
- Expand Security Settings, and then expand Local Policies.
- Select the User Rights Assignment folder.
- In the pane, double-click Lock pages in memory.
Figure 1 – Windows Group Policy tool. - In the Local Security Policy Setting dialog box, click Add.
- 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.
