Lock Pages in Memory in SQL Server 64-Bit

Hello All,

Well after a long time of no writing, I finally found an interesting topic and also some time to write.

Lately, I’ve encountered couple of instances of Troubleshooting Memory Pressure on SQL Server 2005 64-Bit version. Doing some basic configuration check, one realizes that ‘Lock Pages in Memory’ Operating System Privilege is not granted.

On further probing, came a common question:

Question:         “Weather we require Lock Pages in Memory in SQL Server 64-Bit”?

Answer:             YES

Here’s why:

In a typical scenario of SQL Server witnessing memory pressure, OS can page out SQL Server Pages. Enabling Lock Pages in memory will prevent operating system from paging out the working set of the SQL Server process.

Per SQL Books online (BOL), “This 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”

BOL further says, “Locking pages in memory is not required on 64-bit operating systems”. This however has some exceptions. In SQL Server 2005 64-bit, there are various memory related issues can be encountered causing SQL Server ‘Performance Degradation’. For more details, refer >> Knowledge Base https://support.microsoft.com/kb/918483

The suggested workaround here is to implement ‘Lock Pages in Memory’

Next steps:

How to Enable Lock Pages in Memory, refer SQL Server Books Online >> https://msdn.microsoft.com/en-us/library/ms190730(SQL.90).aspx

Note: After enabling, lock pages in memory, kindly restart the SQL Server service for changes to take effect.

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.