SQL Server and the “Lock pages in memory” Right in Windows Server

Another Windows setting you might want to enable is “Lock pages in memory”. There is some controversy within the SQL Server community and within Microsoft about whether and when you should enable this setting. Before you decide whether you want to do this, let me give you some background. When SQL Server 2005 was first released (in early 2006), and was installed on systems running x64 Windows Server 2003 or x64 Windows Server 2003 R2, it quickly became pretty common to enable “Lock pages in memory”, to try to prevent the operating system from periodically paging out a large amount of memory from SQL Server’s working set, which would cause a very noticeable bad effect on SQL Server performance.

The reason that this usually happened is because the operating system would run low on available memory (typically due to buggy device drivers), and then the operating system would page out a large portion of the working set of the SQL Server process in order to free up memory for the operating system. This made the operating system happier, but had a terrible effect on SQL Server, since it would have to heavily access the storage subsystem to read back in the data that had recently been the buffer pool.

SQL Server 2005 and above is designed to perform dynamic memory management based on the memory requirements of the current load on the system. On a Windows Server 2003 or later operating system, SQL Server can use the memory notifications from the QueryMemoryResourceNotification Windows API. This is meant to keep the operating system from paging out the working set of the SQL Server process, and it helps keep more database pages available in memory to reduce your physical I/O needs.

The problem was that SQL Server 2005 did not always react quickly enough to a low memory notification from the operating system, so the operating system would take matters into its own hands, and force SQL Server to release a large portion of its working set. Using “Lock pages in memory” prevents this from happening, at the cost of masking the root cause of the issue.

Initially, this setting would only be honored by SQL Server 2005 Enterprise Edition. Microsoft’s official stance was that if you encountered this issue, you should open a support incident with Microsoft CSS to try to find the root cause of the problem. Their opinion was that it was better to find the cause of the problem instead of using “Lock pages in memory” as a Band-Aid to cover up the problem.

Many production DBAs disagreed with this idea. Once you had been affected by this issue a couple of times, and had to explain to your management team or customers why your application suddenly started timing out for no apparent reason, you were usually quite ready to enable” Lock pages in memory”. Later, after a lot of pressure from the SQL Server MVP community, Microsoft finally made “Lock pages in memory” available in SQL Server Standard Edition (in later builds of SQL Server 2005, 2008, and 2008 R2), by using a startup trace flag of 845.

Microsoft has published conflicting information about this issue over the years. The current official stance is that you should not have to use “Lock pages in memory” with Windows Server 2008 and newer, because of improvements in memory management and improved device drivers. Off the record, I have heard several Microsoft employees concede that it may still be necessary to use this setting in some situations. I don’t want people to race out and immediately enable “Lock pages in memory”. Instead, you should consider your specific situation. and use your own judgment before you decide what to do. If I was running SQL Server 2005 on top of Windows Server 2003, I would be more tempted to use this setting. If I was running SQL Server 2008 R2 on top of Windows Server 2008 R2, I would be much less tempted to use this setting. You also have to consider whether you have run into the problem that LPIM was designed to alleviate. Remember the old saying “Don’t fix it if it ain’t broke”…

You have to grant this right to the Windows account that the SQL Server Service is using. This would normally be a Windows domain account. You can do this by using the Local Group Policy Editor on the machine where SQL Server will be running. You can just type GPEDIT.MSC in a Run window, which will bring up the Local Group Policy Editor shown in Figure 1. Then you go to Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assignment.

Next, in the right hand portion of the dialog window, you simply right-click on “Lock pages in memory” and select Properties, and click on the Add User or Group button. Then you need to add the name of SQL Server Service account, and click Ok. You have to restart the SQL Server Service to have this setting take effect.

 

image

Figure 1: Using the Local Group Policy Editor to grant the “Lock pages in memory” right to the SQL Server Service account

If you do enable “Lock Pages in Memory”, it is very important that you also set an explicit MaxServerMemory setting for your SQL Server instance, which I discussed in more detail here. This will control how much memory can be used by the SQL Server Buffer Pool, allowing a cushion for the operating system and anything else (including other SQL Server components) that may be running on the instance or machine.

You can confirm that the user right is used by the instance of SQL Server by making sure that the following message is written in the SQL Server Error Log at startup: “Using locked pages for buffer pool”.

About these ads
This entry was posted in SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and tagged . Bookmark the permalink.

58 Responses to SQL Server and the “Lock pages in memory” Right in Windows Server

  1. Claire says:

    Hi Glen,
    Thanks for the explanation! Quick question– why wouldn’t you want to use LPIM iwth SQL Server 2008 R2 on top of WS 2008 R2?

    • Glenn Berry says:

      Hi Claire,
      Both Windows Server 2008 R2 and SQL Server 2008 R2 have better memory management, and they communicate with each other better about their memory status. This means I would be much less inclined to use LPIM with that combination.

      • Brett Hawton says:

        Hi There,
        It’s my understanding that it’s still better to use Lock pages in memory even on the latest revisions of the 64 bit Win OS for 2 other reasons not mentioned here:
        1. It allows SQL Server to use the AWE API set to allocate and reference memory. The AWE mem API’s are more efficient than the standard Windows mem API set.
        2. It corrects NUMA page residency issues. If a memory area is paged out from NUMA node A but then a thread in NUMA node B requires it then it will be loaded into Node B memory hence thwarting SQL Server’s NUMA awareness. See here: http://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx
        Brett Hawton

  2. Is there any harm in enabling it on a dedicated SQL Server with appropriate max memory cap in place regardless of version? Is it not worth doing just to be safe?

    • Glenn Berry says:

      If it is just the SQL Server Engine on a dedicated machine, the risk of using LPIM would be pretty low, as long as you had MaxServerMemory set to an appropriate value. The possible risk would be masking a problem with a memory leak in a device driver, that might cause the OS to become unstable as it became critically low on memory.

      • Thanks – I should have mentioned on my previous comment, but nice post! I didn’t realize faulty device drivers were the original reason behind the recommendation to use that setting.

  3. Pingback: @GlennAlanBerry posts SQL Server and the “Lock pages in memory” Right in Windows Server | sqlmashup

  4. Kevin Kline says:

    Great info, Glenn! I was just explaining to a customer today the importance and significance of this setting. They naturally followed up with “Have anything I can show my Windows admins?”

    And thanks to you, I could grin and say “Why, yes. Yes, I do.” LOL.

    -Kev

  5. Tom Powell says:

    Good post, thanks for the information.

    What kind of symptoms will I see to point me at LPIM? “General slowdown” is kind of, well, general.

    Thanks!

    • Glenn Berry says:

      Tom,

      You will see messages like this in the SQL Server Error Log:

      A significant part of sql server process memory has been paged out. This may result in a performance degradation.

      You will also see sudden symptoms of memory pressure and I/O pressure. This is one of several Microsoft KB articles that goes into more detail:

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

  6. Pingback: Something for the Weekend – SQL Server Links 18/02/11 | John Sansom - SQL Server DBA in the UK

  7. John Couch says:

    Hi Glenn. Great post! I do have a question though. Most of our OS’s are x64, and even though I have set the value for LPIM with the service account, I have never seen a message in the SQL log stating “Using locked pages for buffer pool” on any of the servers. Did I miss something?

    • John Couch says:

      Ok, so, now that I posted this, i just manually restarted a DEV box, and I see the message. This is on a SQL Server 2008 instance running on Windows 2008 R2, both are EE editions. I still don’t see the entry in the log on my SQL Server 2005 instance though.

  8. Glenn Berry says:

    John,

    I could be mistaken, but I think that message in the SQL Server Error Log was added in SQL Server 2008

  9. Jay says:

    Hi Glenn, What do you think about enabling LPIM on Win 2008 and SQL 2008 Sp2?
    Thanks
    Jay

  10. After speaking with a PFE and digging into some KB articles, it appears the Microsoft no longer encourages setting MaxServerMemory:

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

    Improvements in Windows Server 2008
    Windows Server 2008 improves the contiguous memory allocation mechanism. This improvement lets Windows Server 2008 reduce the side effects of paging out the working set of applications when new memory requests arrive. If you are running SQL Server on a Windows Server 2008-based computer, you do not have to manually configure the max server memory property as specified in the “Important considerations before you assign the ‘Lock pages in memory’ user right for an instance of a 64-bit edition of SQL Server ” section.

    • Glenn Berry says:

      You will hear advice and see references going both ways on whether to enable LPIM and whether to set MaxServerMemory at the instance level. My current opinion is that I still recommend that you do set MaxServerMemory to an explicit value rather than leaving it at the default value. WIth SQL Server 2008 R2 and Windows Server 2008 R2, I no longer enable Lock Pages in Memory.

  11. Peter Zhang says:

    Glenn,
    Thanks for the insight.
    One question: If I pin data pages in memory, how does that affect lazy writer process? Does it it mean the lazy writer will have no work to do, since even dirty pages must stay in memory? So only process that writes data back to disk is CheckPoint?

    Thanks in advance for your input.

    Peter

  12. Janssen says:

    Hi Glenn,
    This is very informative and I am trying to link to a recent incident we encounter where our SQL Server 2005 Ent. Edition running on Windows Server 2003 32-bit suddenly uses double the CPU after a reboot of the server. We saw high AWE Unmap calls/sec and this issue went away after we failed over to another node in the cluster. I wonder if this was caused by the same issue you mention here? Thanks.

  13. Randy Redekopp says:

    Hi Glenn

    Good post. I have SQL 2008-R2 running on Server 2008-R2 in a VMWare virtual machine. There is a ‘solutions guide’ from EMC (our storage vendor) that says to enable LPIM. Do you have any opinions on how a virtual environment affects your decision to enable this setting?

    Thanks
    -Randy-

  14. Rohit says:

    Hey Thanks Glenn its really helpful for me :)
    Keep Posting

  15. Mark Freeman says:

    Glenn,

    I am stuck using a Windows Server 2003 on 32-bit hardware with 4.75G of RAM for a development server. In that, I am running 2 instances of SQL Server 2005 Standard (database only) and 2 instances of SQL Server 2008R2 Standard (SSRS and only its databases, very light usage). Obviously, memory pressure is a constant issue and I get the “swapped out” message in the log whenever BackupExec does a full backup of this server (excluding the databases, of course). Would you recommend setting LPIM for any of these instances? What about the MaxMemory setting?

    • Glenn Berry says:

      Mark,

      Do you really have hardware that is 32-bit only (confirmed by CPU-Z)? If so, your CPU must be at least 5-6 years old, which is really ancient. You can probably buy or build a desktop “server” with a Sandy Bridge Core i5 or Core i7 and 16GB of RAM for less than $1000 that would be so much better than what you have…

      As I recall, you have to use the /pae switch on boot.ini with 32-bit Windows Server 2003 to be able to see more than 4GB of RAM. Really, running four instances of SQL Server with less than 5GB of RAM, is going to be a problem, no matter what you do, unless there is virtually no activity against the server. Assuming you cannot move to any better hardware, I would set Max Server memory on each instance to try to allocate their memory usage by priority somewhat.

      • Mark Freeman says:

        Glenn, Thanks for the quick reply! We’re likely to get a new server this year, but I have to keep this antique (Dell PowerEdge 2650) chugging along for a while longer. We just bought a beefy new box for hosting all our virtual QA labs and another for hosting our TFS and build boxes, so this one is really just running our development databases.

        We do have /pae in the boot.ini. Would you recommend LPIM for this situation? Would adding /3GB help or hurt? Assuming that the 2008R2 SSRS instances are only asked to serve up a report a few times per hour and almost never more than one at a time, how low can I set max server memory without causing more problems than I’m fixing?

  16. Pingback: Something for the Weekend – SQL Server Links 18/02/11

  17. Kenneth says:

    Hi Glen,
    I am trying to use this LPIM setting in this environment:

    Microsoft SQL Server 2005 – 9.00.3080.00 (Intel X86) Sep 6 2009 01:43:32 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    I am using this because I have enable PAE and AWE. However, my error log has an entry that says Message
    ‘Address Windowing Extensions is enabled. This is an informational message only; no user action is required.’

    but the agent log also has

    ‘[310] 8 processor(s) and 4096 MB RAM detected’

    It’s seeing only 4GB of RAM though there is 16 GB in the server.

    What coul I be doing wrong?

    • Glenn Berry says:

      Since this is x86 on Windows Server 2003:

      Do you have the /PAE switch in your boot.ini file?

      Do you have AWE enabled with sp_configure ?

      Have you granted the “Lock Pages in Memory” right to the SQL Server Service account, and then restarted the SQL Server Service?

  18. Frank Garcia says:

    Would you recommend this setting on a Windows 2008 R2 (64bit) running SQL Server 2008 SP2 (64bit). The SQL Server is not R2 version. This environment is also on VMware ESX 5

    • Glenn Berry says:

      I tend to recommend using LPIM more often than not nowadays. It is very important that you also set an appropriate value for Max Server Memory for the instance, especially if you enable LPIM.

  19. ashish says:

    which sql server 2008 r2 supports lock pages in memory in standard 64 bit edition? is it sp1+cu2 or just sp1 with trace flag

  20. Rizwan says:

    Hi Glenn,

    My Lock Pages in Memory Properties has Add User greyed out. I am installing SQL 2008 R2 SP1 x64 on Windows 2008 R2 x64. Any Suggestions

    • Glenn Berry says:

      I would guess that you are not a local admin on the machine, with the user credentials that you are currently logged in with.

      • Rizwan says:

        I gave it a try with local admin account but same result… thanks for the suggestion. I appreciate it

  21. Dan says:

    Lock Pages in Memory is blank, but when I query my memory in sql server, locked_page_allocations_kb from dm_os_process_memory is almost all of my memory. Is this ok? Shouldn’t it be 0 if it’s not set. This is server 2008r2, sql server 2008 r2

    • Glenn Berry says:

      I am guessing that the account that you are using for the SQL Server Service is a local admin on your machine, so it gets that Windows right by default.

      • Dan says:

        Thanks, i found out the @@servername is different than the hostname, so it is inheriting it from somewhere else, and I’ll need to change that.

  22. Ruan says:

    Hi Glenn,

    Thanks for the nice post. We have SQL Server R2 (SP1) SE x64 running on Windows Server 2003 (SP2) x64. In the error log I see a lot “a significant part of sql server process memory has been paged out” errors. I have added our sql service to the policy but have only found out today that the trace flag -T 845 needs to be added in the startup for R2 as well.

    What I want to know is, how will I be able to determine if this will have a positive/negative affect beforehand?

    Regards

    • Glenn Berry says:

      There is no iron-clad way to determine the effect beforehand. If you have been seeing those error messages in your SQL Server Error Log, it is a good idea to try enabling LPIM. Make sure that you set the Max Server Memory setting to an appropriate value based on how much physical memory is in your server, what SQL Server components are running, and what else is running on your server.

      I am curious why you are running SQL Server 2008 R2 on top of Windows Server 2003? Windows Server 2003 is out of mainsteam support from Microsoft, and Windows Server 2008 R2 behaves much better as far as memory management goes.

      • Ruan says:

        Unfortunately the client can’t upgrade at the moment as the budget won’t allow it, so we have to make use with what we’ve got.

        It’s a dedicated SQL server running only the db engine, no ssis, reporting services or analysis services

        Thanks for the reply, I’ll be implementing the change this weekend

  23. Hi, Glenn Berry
    Here is the basic information about my database server.
    Windows edition: windows server 2008, Service Pack 2
    Processor: Intel® Xeon CPU L5335@2.00GHz 2.00 GHz
    Memory (RAM): 4 GB
    System type: 64 bit Operating System

    I have SQL Server 2008.
    My Task Manger shows that “sqlservr.exe” takes 1827800K Memory.
    overall system takes 87% of physical memory.
    I would like to know is this a normal memory usage?
    Please tell me how I reduce the memory usage of SQL? I set the max server memory to 2048 MB.

    • Glenn Berry says:

      That behavoir is normal for SQL Server. It uses the SQL Server Buffer pool to cache data and other things (like execution plans) in memory by design. That is why you use the Max Server Memory setting to limit the size of the buffer pool. That is how it is designed to work.

  24. Kalyan says:

    Hi Glenn,

    We have “SQL 2005 Standard” running on Windows 2008 R2. This is a 8 CPU and 16GB Ram box and I have set maxmemory to 13.5GB to allow some room for the OS and other operations. This is a stand alone box for SQL Server.

    After a day or two I have noticed that SQL Memory is around 14.5GB and never coming back even though the CPU usage is nomral (10-15% peaks and coming back).

    Can you please let us know what can be done here and I am concerned about SQL Server taking too much memory and eventually not responsive.

    We don’t have LPIM setting enabled.

    Thanks for the great article.

  25. Kalyan says:

    Thank you for the quick response.

    I am not sure if I noticed the similar behavior on the old server which is Windows 2003 and SQL Server 2005 Standard.

  26. Ashwani Malik says:

    Glenn, Thanks for the great post. I have a question- when SQL server reboots it starts from few MB and acquire memory as needed to support the workload. Is there anyway to keep the allocated memory to buffer pool and do not page out to OS if the system is rebooted? Allocation of memory hits the performance of our trading system after the reboot. Is there any way to avoid it?

  27. Andrew says:

    Hi Glen,
    I am on a desperate situation right now,I was wondering if you could give us some help! let me explain what our problem is and you might be able to help with some advice. Our finantial software runs off a sql server 2005 database, the server is a Windows 2k3 Enterprise (x86) with 12 GB of RAM and Xeon quad 2.33 GHz. Before sqlsrv.exe was running at 2.7 gbs (from task manager)…investigating was adviced to enable AWE, lock pages in memory, and added /pae on boot.ini.
    Now sqlsrv.exe runs at 130 mb on task manager but PF Usage sky rocketed to 7.30 GBs…clients PCs get frquently kicked out of the finantial program with a “not responding” status….
    I just found out that my sql server version is RTM….should I install sp4 for sql server 2k5?
    Need help please!!

    • Glenn Berry says:

      Installing SQL Server 2005 SP4 might help a little bit. Really, you need to be on x64 to efficiently use all of your memory. When you have /PAE on x86, Task Manager does not report the actual memory usage by SQL Server. The extra memory that is visible due to /PAE and AWE and LPIM is only usable by the SQL Server buffer pool.

      You have very old hardware, running an OS that is out of mainstream support, along with SQL Server 2005 that is also out of mainstream support. I cannot tell what you biggest issues are from what you have told me.

    • John Couch says:

      Does the application support SQL server 2005 SP4? If so, then I would say yes. What else is running on the system?Did you change the memory settings on SQL Server to use more memory? If so, how much? What does SQL server show in private bytes, working set…etc? What other switches are enabled on SQL Server? How is your TempDB configured? Are your drives aligned? Whats the CPU configuration?

  28. Andrew says:

    Glen/John, Thank you for your comments, John, I changed the memory settings in sql server to use min memory 1024 MB and max 10000 MB. I am not an expert on Databases or SQL so I am a but lost with everything else you are asking me, sorry. our database is around 4.6 GBs in size.
    this accounting server has only one cpu Xeon Quad at 2.3 GHz..

  29. Michael Chau says:

    what about use the MAX memory configuration on SQL Server.???

  30. Scott says:

    I ran across this article while researching SQL Server 2008 R2 best practices. I’m getting ready to install it on a Windows 2008 R2 Server Enterprise Ed. VM (XenServer w/Compellent SAN). I think I’m going to chance it and NOT set LPIM for the SQL Server Service AD account.

    That being said, I can give a real world example where that probably would have solved/hidden an issue for me about a year ago. We have two SQL 2005 VMs running on Windows 2003 Server x64 (LPIM not enabled — still not to this day). On one of the servers, I started getting reports from users about performance issues, and saw the “A significant part of sql server process memory has been paged out” warnings in the SQL Error Logs. SQL Server memory usage went from about 28 GB to about 100 MB in a split second. This was happening every two hours — it was horrible. I had no clue what was causing it. The other server did not experience this issue. It turns out that the AV software we use was the culprit. We were supposed to be getting AV updated from a local repository, however, the problem server was reaching out to the AV provider’s site instead. I don’t know why this caused a problem, but as soon as I changed the setting to hit the local AV repository the problem went away.

  31. Vasyl says:

    Hi Glenn,
    Not sure if you are still monitoring this topic but I have a question for you:
    You state:” With SQL Server 2008 R2 and Windows Server 2008 R2, I no longer enable Lock Pages in Memory.” And we don’t do it. A problem is – OS run quickly out of memory and crushed when SQL instances eat all available memory. We use SQL Server 2008 R2 on Windows Server 2008 R2.
    We run a clustered SQL and when SQL instance is migrated from another note, the ‘main’ node run out of the available memory very quickly and crushed. We had to set MaxServerMemory below ½ total memory to prevent it (for 2 notes and 2 SQL instances).
    Why the better memory handling with SQL 2008 R2 is not working for us? Any idea?

  32. Shadi says:

    Hi Glen,
    Is it ok if I have 4 instances of SQL on a server and set the LPIM on the server? then what is happening to other instances if one instance lock the pages in memory?
    By the way, I have SQL Server 2008 64 bit not R2 on all instance and windows server 2008 R2 64 bit.

    • Glenn Berry says:

      Each instance will try to grab as much memory as is set in the Max Server memory setting when SQL Server starts up for that instance.

      You need to keep that in mind, and set Max Server Memory low enough so that all four instances together leave enough RAM for the OS.

  33. I initially came to this page because I was having memory issues and I thought by the error this might have something to do with it. Turns out it was the Sophos antivirus software on the sever that was running junk on sql server by default that caused these out of memory errors. This is just an FYI if you’re reading this.

  34. GS Sohal says:

    Great Post Glenn …

    the comments and replies are worth reading .. Thanks !!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s