What Does Lock Pages in Memory Do

I thought I would post some clarification on what the famous "Lock Pages in Memory" policy setting does and does not do.

1. It prevents the operating system from stealing the buffer pool (see previous blog on buffer pool), thus shrinking the working set.

2. It is NOT available on Standard Edition.

3. It ALSO enables an enterprise feature called "Large Page Extensions" on 64 bit systems. This allows SQL Server to allocate memory in 4-16MB chunks. This is order of magnitudes faster than the piddly default 4k size and highly recommended for processing huge queries.

3. You can tell if Lock Pages in Memory is enabled by looking in the sql server log for the entry "Large page extensions enabled" in the log.

4. It does not stop the operating system from shrinking the working set. The Thread and MemToLeave areas can still be bashed causing pressure. (see previous blog).

5. It is automatically enabled on 32 bit systems if AWE is used. As we said earlier. AWE sucks, so no big gain here ;-)

Comments

• Anonymous
  August 20, 2009
  Hi,I think some clarification are required.First "Lock page in memory" is not SQL Server feature, it is just a Windows privilège: http://msdn.microsoft.com/en-us/library/ms813937.aspxThis privilege is granted by default to the local system account.Regarding point 1:This privilege allow a win32 process to prevents the operating system to put the memory in the swap file.This is not the case if the win32 process is not asking for.Regarding point 2:"Lock page in memory" is not SQL Server feature, then not linked to SQL Server edition. Maybe you’re speaking about AWE or large page allocations…