What does lock pages in memory do as stated on the Setting SQL Server memory options in our PME 8.x installation manual?

Search FAQs

You are currently viewing the content available in Vietnam. If you are looking for information for another region, please select the correct country from the top-left dropdown in the page and 'Navigate to Browse FAQs' in the Support menu.

Issue
The PME 8.x installation manual states to consult the site administrator to determine where or not to enable the Lock pages in memory permission setting in Windows for The SQL service account on all SQL server instances. What exactly does this setting do?

Product Line
PME 8.x.

Environment
PME 8.x, Microsoft Windows, SQL Server

Cause
This note from the installation manual is not clear of what the permission setting does.
In addition to setting the maximum memory option, consult with your site administrator to
determine whether or not to enable the Lock pages in memory permission setting in
Windows for the SQL service account on all SQL Server instances.

Resolution
Lock Pages in Memory
This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. Locking pages in memory may keep the server responsive when paging memory to disk occurs. The SQL Server Lock Pages in Memory option is set to ON in instances of SQL Server 2016 Standard edition and higher when the account with privileges to run sqlservr.exe has been granted the Windows "Locked Pages in Memory" (LPIM) user right.

Virtual memory systems allow the over-commitment of physical memory, so that the ratio of virtual-to-physical memory can exceed 1:1. As a result, larger programs can run on computers with a variety of physical memory configurations. However, using significantly more virtual memory than the combined average working sets of all the processes can cause poor performance.