Do You Enable “Lock Pages in Memory”?

Posted on by

image

Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems, and depending on the circumstances, the following conditions could occur when this setting is not turned on:

1) Performance of SQL Server could suddenly decreases for no apparent reason.

2) The hardware running SQL Server could suddenly appear to stop responding for a short period of time.

3) Applications connected to SQL Server could time out.

4) Among many others.

This sudden performance degradation can occur when Windows tells SQL Server that it needs more physical memory, and SQL Server, in response to the request, begins to swap the data out of RAM (such as the data cache) to disk, killing performance. Obviously, this is something that you don’t want to have happen on your production server.

Microsoft, and many SQL Server experts, recommend that in most cases, the Lock Pages in Memory setting be turned on for 64-bit SQL Server instances when running under Windows 2003 and earlier. With this option turned on, SQL Server will refuse Window’s request for giving up its memory, this preventing the problems described above. This problem occurs much less in SQL Server instances running under SQL Server 2008, and because of this, turning on Lock Pages in Memory is most likely not needed, and many SQL Server experts are now recommending that it not be turned on when using Windows 2008 unless you actually experience this problem.

According to the poll, 40% of those responders who have 64-bit systems turn this setting on, while another 30% don’t. I don’t know if the 30% who don’t turn it on are deliberately not turning on for a specific reason (perhaps because they are running under Windows 2008), or they are not turning it on because they don’t know about the potential benefits it offers.

Another 30% haven’t heard of this option, which is a shame, as this setting can have significant performance effects on a server, and DBAs need to know more about it. If you want to learn more about this option, check out these resources.

Before turning Lock Pages in Memory on for your SQL Server instance, be sure to carefully read the article “How to Reduce Paging of Buffer Pool Memory in the 64-Bit Version of SQL Server” listed above. It will provide you the information you need to know before you make the decision as to whether or not you want to turn Lock Pages in Memory on for a particular SQL Server instance. Before making any changes in SQL Server, it is very important that you test them before implementing them in production.

8 thoughts on “Do You Enable “Lock Pages in Memory”?”

  1. I have been seeing these exact types of issues with a SQL Database this week. The application connected to this database has experienced random time outs, among other performance issues. I was unable to find how “Lock Pages In Memory” effects Win2003 32bit systems. On my Win2003 32bit system I did find that “Lock Pages In Memory” is not turned on. How does “Lock Pages In Memory” effect 32bit systems?

  3. For me, locking pages in RAM is a no brainer. As a DBA who cannot always control what is installed on the production database server, my goal is to prevent other applications from stealing RAM from the database engine. Time and time again, locking pages in RAM has done this.

  4. BEWARE using Lock Pages in Memory willy-nilly:

    http://support.microsoft.com/kb/918483

    “You should make additional considerations before you assign the ‘Lock pages in memory’ user right. If you assign this user right on systems that are configured incorrectly, the system may become unstable or experience a performance decrease of the whole system. Additionally, event ID 333 may be logged in the event log.”

    Kevin Boles
    @TheSQLGuru

  5. I’ve run into similar scenarios like this in production servers that were not properly configured for max memory, or had other tasks/programs running in the back ground requiring more memory from the os. Some times it was moving a backup file off-server – in those cases we saw Memory Pressure errors popping up in the SQL Error Logs when Windows requested SQL free up memory. So – make sure you don’t leave the max memory setting in SQL Server = total server ram, leave plenty of RAM for windows to do whatever it needs so it never needs to request memory back from sql. This was in SQL 2k5/Server 2k3 combo, so sql/server 2k8 might not have the same issues.

  6. Hi All,
    I’d like to add shortly:

    Lock pages in memory will have effect only for SQL Server 2008 Enterprise edition and
    SQL Server 2008 Standard edition ONLY with SP2 applied. MS opened this feature for Standard Editons in SP2.

  7. Oleg, see http://support.microsoft.com/kb/970070 for a list of all the SQL Server 64-bit versions and editions that support “lock pages in memory.” With the proper service packs and cumulative updates, it is also suppported in the 64-bit standard and enterprise editions of both SQL Server 2005 and 2008.

  8. Wow, thanks for all the useful information everyone. This was a big help. Now I just need to go test this solution, then convince leadership and the windows dudes to let it happen.

Comments are closed.