What is Lock Pages In Memory? – Interview Question of the Week #186

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.

What is Lock Pages In Memory? - Interview Question of the Week #186 lockpagesmemory

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.

What is Lock Pages In Memory? - Interview Question of the Week #186 lockpages1

(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)

What is Lock Pages In Memory? - Interview Question of the Week #186 lockpages2

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)

What is Lock Pages In Memory? - Interview Question of the Week #186 lockpages3

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)

What is Lock Pages In Memory? - Interview Question of the Week #186 lockpages4

I guess that’s it. It is that simple of a process.

Reference: Pinal Dave (https://blog.SQLAuthority.com)

, , ,
Previous Post
How to Identify Process ID for SQL Server Services? – Interview Question of the Week #185
Next Post
Which is Faster – INSERT INTO SELECT or SELECT INTO? – Interview Question of the Week #187

Related Posts

Leave a Reply

Menu