Question: What is Lock Pages In Memory?
Answer: 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 set up properly by reducing the amount of available RAM for your system.
How to Enable Lock Pages In Memory?
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.
Reference: Pinal Dave (https://blog.SQLAuthority.com)