Recently I received an email from user that he tried to search for solution on SQLAuthority.com for how to enable lock pages in memory but could not find a solution for it.
Well, here is a simple tutorial for the same.
Step 1: Go to Windows Start Menu and search for either “gpedit.msc” or “Edit Group Policy”. Now click on either of the options.
(Alternatively, you can search for this option in Control Panel as well).
Step 2: Follow the path to reach to Lock Pages in Memory Settings
Local Computer Policy >> Computer Configurations >> Windows Settings >> Security Settings >> User Rights Assignment >>(On Right Side Window you will see Lock Pages in Memory Settings)
Step 3: Right Click on “Lock Pages In Memory” Policy and open properties windows.
Step 4: Click on Add User or Groups… >> Click Advanced >> Search login/user which is running SQL Server >> click OK >> click OK (once again)
Step 5: Now you will have to logout and login again to make this policy effective for that user. (In simple words – Restart SQL Server)
I guess that’s it. It is that simple of a process.
What Actually Lock Pages In Memory do?
Well, after reading this blog post, if you are still wondering what actually lock pages in memory do. Well, it is just a security setting for accounts. When enabled, it allows accounts to keep data in physical memory, instead of paging it to virtual memory on disk.
In simple words, Lock Pages in Memory can help improve your system’s performance in the most cases.
There is a small gotcha as well – If you enable this feature, there are chances that it may also negatively impact your performance if not setup properly by reducing the amount of available RAM for your system.
If you have any question, leave a comment. I discuss this in detail in my workshop – SQL Server Performance Tuning Practical Workshop.
Reference: Pinal Dave (https://blog.sqlauthority.com)
How can we do this for A SQL Login ?
How does this affect performance on virtual machines?
is there a command line to add an account to lock pages in memory
You need to run PowerShell to do that.