Lock Pages in Memory in SQL Server 64-Bit

  • Article

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.

Comments

  • Anonymous
    February 24, 2009
    PingBack from http://www.clickandsolve.com/?p=14153

  • Anonymous
    February 25, 2009
    The real question is, "How do I lock pages in memory with the standard version of SQL Server?" My understanding is that this only works with the enterprise version. We had issues every day when our backup would run.  It would copy the backups created off of the SQL Server.  Sql Server would give up all of its memory.  The system would slow down to a halt. It would fail over to its mirror. This is not good. Is this issue fixed in SQL 2008?

  • Anonymous
    February 25, 2009
    Thanks Scott for clarifying this ! In regards with your query, 'Lock Pages in Memory' is supported with Standard Edition. Please refer 'APPLIES TO' section of KB http://support.microsoft.com/kb/918483 Thanks,

  • Anonymous
    March 14, 2009
    Going through Varun's blog on Lock Pages in Memory in SQL Server 64-Bit , I remembered an issue that

  • Anonymous
    September 10, 2009
    So, is this still beneficial in SQL 2008?  I only see references out there for SQL 2005.

  • Anonymous
    September 17, 2009
    Please be aware, By default, Enterprise and Developer 64-bit editions of SQL Server 2005 and of SQL Server 2008 support Locked Pages. For more information, refer below MSDN link: http://msdn.microsoft.com/en-us/library/ms187499.aspx