Click here to monitor SSC
  • Av rating:
  • Total votes: 91
  • Total comments: 25
Jonathan Kehayias

Great SQL Server Debates: Lock Pages in Memory

12 December 2011

There has been much debate over the need for the Lock Pages in Memory privilege, on 64-bit versions of SQL Server. Jonathan Kehayias presents a "warts and all" account of its history, the confusions surrounding its use, and why he believes it's still a good default configuration for 64-bit SQL Server instances, even when running Windows Server 2008 and Windows Server 2008R2.

"An ounce of prevention is worth more than a pound of cure." – Benjamin Franklin

Recently, I responded to the following, seemingly-innocuous question, on Twitter: "Should I use Lock Pages in Memory as a Default Configuration?" My answer was yes, if it's 64-bit, you have more than 16-32 GB RAM installed, you've, set 'max server memory' appropriately, and you monitor the Memory\Available Mbytes counter, then you should enable it by default. In the ensuing debate, it became clear that my advice was somewhat out-of-step with that offered on this topic by the CSS team at Microsoft, as well as several respected SQL Server MVPs; not on the point that the Lock Pages in Memory privilege was still sometimes needed, in response to the Operating System forcing SQL Server to trim its working memory set, but on the point that it should be assigned by default.

With their kind permission, I'm going to single out posts by two SQL Server MVPs, and well-respected members of our SQL Server community, Brent Ozar and Glenn Alan Berry, which summarize the countervailing opinion:

In other words, on older Windows Server versions, which responded aggressively to memory pressure by trimming SQL Server's working set, then use of LPIM was highly advisable. However, with new operating systems, with improved memory management, it's better to not to assign the LPIM privilege, unless it's really required. In many ways, this is sound advice, and even as recently as early 2011, when I first joined SQLskills as a consultant, I would have agreed with it entirely. However, my experiences since then have convinced me otherwise. In this article, I hope to set out, definitively, the major issues surrounding use of LPIM, and to explain why I recommend that Lock Pages in Memory be used as a default configuration option on all 64-bit instances of SQL Server, unless you have a good reason not to (such as use of a virtualized environment).

Essentials of SQL OS Memory allocation and management

The Windows Operating System runs every process, including the SQL Server process, in its own dedicated area of virtual memory, known as the Virtual Address Space (VAS). The VAS is divided into two regions; kernel mode (or system) space and user mode (or application) space. The kernel mode VAS is used by the OS, for mapping various system data structures such as the file cache, Paged and Non-Page pools. The user mode VAS is used to map memory for the currently-executing application process i.e. SQL Server.

Under default configuration, all SQL Server's memory allocations are made in the user mode VAS, using calls to the VirtualAlloc() Windows API function. Any memory allocated by VirtualAlloc() is pageable, meaning that the Windows OS can force this memory to be paged to disk, in response to memory pressure. Memory allocated by VirtualAlloc doesn't have to be physically present but Windows ensures that the amount of memory committed by SQL Server will be less than, or equal to, the installed physical memory, plus page file capacity.

When the Lock Pages in Memory permission is assigned to the SQL Server service account, then memory allocations for the buffer pool are made using calls to a function in the AWE API called AllocateUserPhysicalPages().All of the memory that is allocated using the AllocateUserPhysicalPages() API are considered locked, i.e. non-pageable, and must be backed by physical memory on the server.

As a general rule, SQL Server will use as much memory as you can give it, and it will not release the memory that it has allocated under normal operations, unless the Windows Server OS sets the memory low resource notification flag. A component of the SQLOS called the Resource Monitor monitors the QueryMemoryResourceNotification Windows Server API and when Windows sets the low memory resource notification, the SQLOS will respond by sweeping its caches internally to reduce the process working set, and release memory back to Windows.

At this point any memory that is pageable, i.e. memory allocated via VirtualAlloc(), may be paged to disk in order to free up more memory for the OS. Conversely, any memory allocated using AllocateUserPhysicalPages() is locked and cannot be paged. In cases where large amounts of memory are locked, it can limit how much memory the Windows OS can reclaim under pressure, and this could lead to system instability.

Nevertheless, the Windows OS will do what it can to reduce memory consumption and any of the SQLOS structures that aren't locked, for example the thread stacks, and any other non-buffer pool memory, can still be paged out. This can cause problem performance issues, but it will rarely impact SQL Server performance in the same way as having 50 GB+ of buffer pool paged to disk, immediately.

The potential problem with using Lock Pages in Memory is that if the SQLOS can't respond quickly enough to a low memory notification, to release the memory that Windows needs, it can cause Out of Memory (OOM) errors in Windows, and instability. The way to avoid this, as we'll discuss a little alter, is to make appropriate configuration changes to prevent Windows from experiencing memory pressure in the first place.

A Brief History of Locked Pages in Memory

Let me start by saying that this whole debate of whether or not to use LPIM is framed entirely within the context of a 64-bit environment. However, in order to understand fully why this topic has caused so much confusion over the years, it's worth briefly revisiting the bad old days of 32-bit.

If you are running 32-bit SQL Server, and need access to more than 2 GB of user mode VAS, then you have to use Lock Pages in Memory; there is no debate there. You must configure the OS to use Physical Address Extensions (PAE), enable Address Windowing Extensions (AWE), and then assign the Lock Pages in Memory permission to the SQL Server account so that it can allocate AWE memory, via calls to AllocateUserPhysicalPages(). So, in a typical 32-bit server using LPIM you'd have, in addition to the 2 GB of pageable user mode VAS, a separate AWE-mapped area, up to 64 GB (the PAE pointer was 36-bit), of non-pageable memory. This AWE-mapped area is for exclusive use by the data cache portion of the buffer pool. The rest of the buffer pool (mainly the plan cache), and other non-buffer pool allocations are still mapped within the 2 GB of user mode VAS.

However, the advent of a 64-bit SQL Server process completely changed the dynamics of memory allocation by the SQLOS. In place of the default 2 GB user mode VAS, for a 32-bit process, a 64-bit process has access to up to 8 TB of user mode VAS out-of-the-box, without any need for further configuration changes! 64-bit users now have a potentially-vast amount of memory for the buffer pool, but all of which is allocated via VirtualAlloc, and backed by user mode VAS, and so is pageable i.e. in the absence of LPIM, the memory allocated for the data and plan cache is pageable.

In 64-bit SQL Server, the SQL Server account still requires the Lock Pages in Memory permission in order to be able to allocate locked pages, via AllocateUserPhysicalPages(), but there are a couple of big differences:

  • The underlying reliance on AWE-mapped memory is removed. You do not need AWE in 64-bit SQL Server; the awe enabled sp_configure option has no meaning. The continued use of the same AWE API function is purely to ensure that the allocated pages are locked.
  • Memory allocated via AllocateUserPhysicalPages() can be used for both the data cache and plan cache. In 64-bit SQL Server, the plan cache is no longer allocated separately (it now uses stolen pages from the buffer pool)

The crux of the problem is that, under a number of conditions, the Windows Server OS may trigger hard working set trims of the running processes, forcing large amounts of memory allocated by SQL Server to be paged out to disk, and leading to performance degradation in the SQL Server environment. Some of the specific scenarios where this can occur have been documented by the Product Support Services group at Microsoft in KB 918483 (http://support.microsoft.com/kb/918483).

This was a particular problem for early 64-bit environments – SQL Server 2005 on Windows Server 2003 – where the OS was aggressive in its requests to trim SQL Server's working set, in response to memory pressure. The problem was greatly exacerbated by the fact that in early 64-bit SQL Server Lock Pages in Memory was an Enterprise-only feature. If you were running Standard Edition, there was nothing you could do to prevent these working set trims.

So, whereas 32-bit users had a relatively small area of pageable memory (the 2 GB of user mode VAS) and then a bigger area of locked memory for the data cache, which was protected from hard trims, early Standard edition 64-bit users had no defense against the OS hard trimming the most significant portion of the SQL Server working set, in response to memory pressure.

If you have a SQL Server with 64GB RAM and 52GB of that is allocated to the buffer pool, these hard trims have a significant performance impact on the server operation, since the entire purpose of the buffer pool is to minimize disk access by caching frequently, or at least recently used pages in memory where the access time is significantly faster than it would by retrieving the pages from disk.

I can only guess at the number of product support cases that were created as a result of this issue, and it took a lot of pressure from the community, and specifically from the MVPs, to have LPIM added to Standard Edition. Finally, the pressure paid off and Bob Ward announced, first at PASS Europe 2009 and then on his blog that SQL Server Standard Edition would finally include the option to use Lock Pages in Memory. This change was released in May 2009 with CU4 for SQL Server 2005 Service Pack 3 and CU2 for SQL Server 2008 Service Pack 1 (http://support.microsoft.com/kb/970070). If you were on Standard Edition, you needed to apply the appropriate cumulative update to be able to enable Trace Flag 845 to make use of Lock Pages in Memory for the buffer pool.

The situation stabilized, and subsequently, in Windows Server 2008, changes made to the memory manager (also documented in the previously-referenced KB article) greatly reduced the problem of hard working set trims for SQL Server. This prompted Microsoft to announce, soon after the release of Windows Server 2008, that Lock Pages in Memory was no longer required.

This brings us more or less back to the current situation, and the advice from the Brent, Glenn and others that if you're running SQL Server 2005 on Windows Server 2003, you need LPIM; if you're running Windows Server 2008, or later, you don't, at least not as a default.

Why Lock Pages in Memory should be a default configuration

I'll present my reasons for the continued use of LPIM, as a default choice, even on Windows Server 2008 and Windows Server 2008 R2, in terms of the major counter-arguments:

  • Improvements in memory management mean it's no longer required – my experience suggests otherwise
  • On 64-bit, use of LPIM can cause OS instability during memory pressure, as it limits the memory Windows can rapidly reclaim through paging – these issues can be avoided by careful configuration of SQL Server memory settings

As a note of caution however, before we start, I refer you to this recent blog post from the SQL Server Support team, regarding a potential bug that could lead to corruption when using LPIM on certain builds of SQL Server 2008 R2 and 2012, on certain unpatched Windows Server installations.

    Hard trims still happen on recent Windows Server versions

    It's certainly true that changes made to Windows Server 2008 memory manager make the problem much less drastic than it was under Windows Server 2003. However, some of the problems listed in the KB article still occur under Windows Server 2008 and Windows Server 2008 R2 and can still result in hard trims of the working set and to serious problems for SQL Server.

    I've worked with numerous clients, who were Windows Server 2008 and Windows Server 2008 R2 and were nevertheless suffering from performance problems that had their root cause in hard working set trims issued at the behest of Windows.

    In some cases, these trims weren't actually being caused by memory pressure on the system; in the worst case the server had 64 GB RAM installed in it, and at the point that the hard trims were being triggered by Windows Server 2008 R2, the server had over 48 GB of available memory!

    What is really insidious about this particular case is that because less than 50% of the SQL Server process memory was getting trimmed, no notifications about the trim were being logged in the SQL Server error log! The only way to track down this problem was to monitor paging, via the Performance Monitor counters for the Process object (as documented in http://support.microsoft.com/kb/918483).

    Over time, I have engaged with countless customers where this has proven to be the cause of their performance issues with SQL Server, and as a result I have reverted to the stance that Lock Pages in Memory should be used as a default configuration if you are running SQL Server on a 64-bit instance of SQL, with more than 16-32 GB RAM, regardless of the version and edition of Windows Server OS that you are running.

    If you are suffering performance problems related to memory trims, then it's very likely that enabling Lock Page in Memory will help. Of course, if you can find out the underlying cause of the working set trim and stop it happening then this is even better. Unfortunately, tracking down the cause of the sorts of problems detailed in the KB article can take a long time, even with the help of Microsoft Customer Support Services, and until you do, you will continue to have performance problems with SQL Server. I would rather prevent issues from occurring in an environment than wait to find out if it might occur and then try to react to performance issues after the fact.

    Preventing Problems with Lock Pages in Memory

    With Lock Pages in Memory, as with everything, there is no such thing as a free lunch and while it can help prevent potential problems associated with hard working set trims of SQL Server, it can also lead to out-of-memory conditions for the Windows Server OS, if appropriate configuration of the overall system has not been made to prevent the OS from getting into memory pressure. Even though the SQLOS is designed to monitor for low memory notifications from the Windows OS through the QueryMemoryResourceNotification API, it is possible that under load, the SQLOS won't be able to respond quickly enough to a low memory condition, and the Windows OS could become unstable as a result.

    As a best practice, even when you're not using Lock Pages in Memory and certainly before enabling it, you need to set an appropriate value for the 'max server memory' sp_configure option, in order to limit the amount of memory that SQL Server allocates for its buffer pool and to leave enough memory available for the Windows Server OS, and other applications running on the server, to be able to operate without triggering memory pressure on the server. These "other applications" include anti-virus software, Integration Services, and any multi-page allocations by SQL Server that occur outside of the buffer pool.

    Unfortunately, there is no hard and fast rule that determines what the optimal value for 'max server memory' will be, for a given instance of SQL Server. The best recommendation I can make would be to set this value artificially low and then gradually fine tune the value, based on monitoring of the Memory\Available Mbytes performance counter in Windows, till you reach the optimum value for the server.

    I tend to start out by reserving 1-2 GB RAM for the OS, and then an additional 1GB for each 4 GB of RAM installed from 4-16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. I then monitor the Memory\Available Mbytes counter over time to determine peak memory usage is for the system. Memory in excess of what's required to support this peak memory usage can be added to the 'max server memory' option.

    At a more technical level, you can perform the necessary calculations for the size of the SQL Server Thread Stack, estimate the usage of memory from multi-page allocators in SQL Server, such as SQLCLR, add in the additional memory requirements for the SQL Server process, and then for each of the applications or services that are running, to try to arrive at a reasonable the starting value for 'max server memory'. My personal experience has been that trying to select a value in this manner tends to result in setting the value too high, and it isn’t always clear that this is the case until you have a problem.

    Considerations for Virtual environments

    Brent, in his previously-referenced blog post, gives the basis of a compelling argument against the use of LPIM, for SQL Servers running in a virtual environment. Having SQL Server running on Virtual machines certainly does pose an interesting problem regarding use of Lock Pages in Memory, since the potential for memory overcommit exists, depending on the hypervisor being used. Memory overcommit is a scenario where the memory allocated to the virtual machines running on the host exceeds the total amount of physical RAM available in the server.

    When memory overcommit occurs, one of the first ways that the hypervisor reacts is to make use of a special driver, known as a balloon driver, which is installed in the VM as part of the VM tools. In essence, the hypervisor sets the balloon driver the task of reducing memory consumption in the VM to a target level, and the balloon driver responds by acquiring memory in the VM. This 'ballooning' activity creates memory pressure in the VM, which in turn prompts the guest OS to reduce the physical memory usage of processes in the VM. The released memory is then available to the hypervisor for allocation to the other VMs running on the host, as necessary to prevent memory pressure at the hypervisor level. Ultimately, if not enough memory can be released, the hypervisor will also begin hard paging VM memory to disk, which can have a huge impact on performance.

    The situation is even more complex when SQL Server is using Lock Pages in Memory, since the ballooning causes memory pressure in the VM, but the guest OS is limited in the amount of memory it can free up by paging to disk. SQLOS will still respond to the memory pressure by reducing its memory usage internally but it may fail to respond quickly enough, resulting in an OOM condition for Windows OS running in the guest, just as if a physical machine ran out of memory.

    There are a number of ways to deal with the balloon driver issues associated with VMs, the worst of which is to disable the balloon driver entirely for the SQL Server VM. The balloon driver exists to allow the hypervisor to manage memory pressure in the best way possible for overall performance of the VMs running on the host, so it should not be disabled as a general rule.

    Instead, if the VM has a condition such that Lock Pages in Memory is needed to prevent hard paging of the SQL Server working set, or the VM needs to be guaranteed to have a minimum set of memory resources, then a reservation should be configured for the VM so that the hypervisor only balloons its memory as a last resort. In a situation like this, it would be best to set the 'min server memory' sp_configure option so that the SQL Server only reduces its memory down to the minimum level required for appropriate application performance.

    Summary

    So there you have it, Lock Pages in Memory warts and all; the history of it usage, the confusions surrounding its use, and why I believe it's still a good default configuration for 64-bit SQL Server instances, even when running Windows Server 2008 and Windows Server 2008R2.

    In my experience, the same problems with hard working set trims, which plagued 64-bit SQL Server instances running on Windows 2003, can and do still occur even under Windows Server 2008 and Windows Server 2008R2, and the potential dangers of using Locked Pages in Memory can be minimized by accurate configuration of 'max server memory', and careful memory usage monitoring.

    Ultimately the decision is yours, but hopefully this article will at least help make that decision better-informed. On this topic, I take the same stance as the great Benjamin Franklin: "An ounce of prevention is worth more than a pound of cure."

    If you’d like to learn more about how to troubleshoot memory management issues, or other common problems than afflict SQL Server, check out the free eBook, Troubleshooting SQL Server, by Jonathan Kehayias and Ted Krueger.

    Next up in the "Great SQL Server Debates" series: Buffer Cache Hit Ratio

    Jonathan Kehayias

    Author profile:

    Jonathan Kehayias is currently employed as a Principal Consultant and Trainer for SQLskills, one of the best-known and most respected SQL Server training and consulting companies in the world. Jonathan is a SQL Server MVP and one of the few Microsoft Certified Masters for SQL Server 2008, outside of Microsoft. Jonathan frequently blogs about SQL Server, presents sessions at PASS Summit, SQLBits, SQL Connections and local SQL Saturday events, and has remained a top answerer of questions on the MSDN SQL Server Database Engine forum since 2007. Jonathan is a performance tuning expert for both SQL Server and hardware, and has architected complex systems as a developer, business analyst, and DBA. He also has extensive development (T-SQL, C#, and ASP.Net), hardware and virtualization design expertise, Windows expertise, Active Directory experience, and IIS administration experience. Outside of SQL Server, Jonathan is also a Drill Sergeant in the US Army Reserves and is married with two young children. On most nights he can be found at the playground, in a swimming pool, or at the beach with his kids. Jonathan can be found online as @SQLPoolBoy on Twitter, or through his blog (http://sqlskills.com/blogs/jonathan)

    Search for other articles by Jonathan Kehayias

    Rate this article:   Avg rating: from a total of 91 votes.


    Poor

    OK

    Good

    Great

    Must read
    Have Your Say
    Do you have an opinion on this article? Then add your comment below:
    You must be logged in to post to this forum

    Click here to log in.


    Subject: Great article, great explanation, and I agree!
    Posted by: Tracy McKibben (RealSQLGuy) (not signed in)
    Posted on: Wednesday, December 14, 2011 at 7:01 AM
    Message: Our production cluster is running 2008 R2 across 48 cores, 512GB of memory. After some trial and error, starving the OS a couple of times, I finally settled on leaving 36GB for the OS, giving the rest to SQL Server with LPIM enabled. This ratio has worked well, no memory issues with SQL or the OS.

    Subject: Excellent Article
    Posted by: Yagnesh (view profile)
    Posted on: Wednesday, December 14, 2011 at 8:23 AM
    Message: Nice Article, I think our discussion triggered this kind of article. Jonathan I would be nice if you have included the combination of min, max and Lock pages in memory scenario.

    For example what will happen if the server has 64 GB RAM , Max memory is set to 54 GB and Min memory set to 40 GB and Lock pages in memory is not set and OS needs lots of memory (30GB) and SQL has to release more than min memory set value . What happens with the similar situation with the Lock pages in memory option is on.

    It is not only agressive trimming but some other thing on OS needs lots of memory and SQL Server has to release its memory.

    Subject: Fantastic
    Posted by: Chris McGowan (not signed in)
    Posted on: Wednesday, December 14, 2011 at 9:09 AM
    Message: Absolutely fantastic article! I'm having problems with SQL Server 2005 x64 on windows 2003 and memory trims. It's an inherited environment, needless to say LPIM will be set once CU4 is installed.

    Great Work!

    Subject: Re: Excellent Article
    Posted by: Jonathan Kehayias (view profile)
    Posted on: Wednesday, December 14, 2011 at 10:15 AM
    Message: Yagnesh,

    My first question would be, what on the server caused a 30GB memory demand that was not SQL? If you have something that can do that, you haven't appropriately planned your memory configuration if you have 'max server memory' set at 54GB, as explained in the article. As for what "might" happen, It Depends....

    Without LPIM, depending on how quickly the memory allocations occur, SQLOS may initially respond by backing off usage and reducing the buffer pool down to the 40GB 'min server memory' setting, but once you are there, SQLOS won't reduce further, and you are likely to face a hard trim by Windows. You haven't given it any other option but to page out the working set.

    With LPIM, SQLOS will back down its usage to your 'min server memory' value. At that point Windows can try and hard trim the other processes, and potentially page the process requiring the 30GB memory out, or if it can't respond efficiently this way, you may face a crash due to Out Of Memory for the OS. Either way, with ~22-24GB paged out, you are looking at a performance impact.

    This is why setting LPIM isn't a magic solution to all problems and why the recommendation is to still track down what is causing the memory consumption leading to the problem. LPIM prevents the buffer pool from being hard trimmed but that isn't the root problem, it is a after effect of a root problem.

    Subject: N-node multi-instance cluster and Lock Pages in Memory
    Posted by: Oscar Zamora (not signed in)
    Posted on: Wednesday, December 14, 2011 at 7:23 PM
    Message: Assuming that there are 2 instances running on 2 nodes (active/active), and both have locked pages in memory for 85% of the RAM available. What would happen to an instance if it fails over to the node that already has Lock pages in Memory? Would it create memory pressure to the failed over instance? What else could happen?

    Subject: Re: N-node multi-instance cluster and Lock Pages in Memory
    Posted by: Jonathan Kehayias (view profile)
    Posted on: Wednesday, December 14, 2011 at 8:54 PM
    Message: Hey Oscar, Yes, that would be something to think about, and there are a few ways to work within that setup as well. The first way, would be to dynamically reconfigure the instances with a startup stored procedure like Aaron Bertrand proposed on his blog post: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/09/18/managing-active-active-cluster-failovers-with-different-hardware.aspx The alternate option would be to leverage 'min server memory' in conjunction with 'max server memory' and rely on the SQLOS cache sweeps and back offs, which may not free memory fast enough to the OS to prevent stability problems. If I had a choice between the two, I would opt with the first option of dynamically re-configuring the instances based on the code example Aaron provided in his blog post, even if you decided not to use Lock Pages in Memory on either instance. I would take this route because I like predictability and control over how my instances are going to be running, and dynamically re-configuring the memory for the instances provides the most predictability for the environment.

    Subject: Great post!
    Posted by: Brent Ozar (view profile)
    Posted on: Friday, December 16, 2011 at 6:40 AM
    Message: Great post, man. I still gotta disagree with setting that option on by default, though, and I wrote about why here:

    http://www.brentozar.com/archive/2011/12/consulting-lines-pilot-dog/

    Subject: MinMemory settings?
    Posted by: DavidAHay (view profile)
    Posted on: Friday, December 16, 2011 at 11:59 AM
    Message: Something I have been doing for years and don't exactly recall why is to set Min and MAX the same so SQL grabs all the memory I want it to have right away with LPIM turned on, while also making sure to leave plenty for the OS. I find myself questioning that practice now after reading your post along with Brent's. I will definitely rethink it, especially on VMs as they come up.

    Back to my question...

    So in your opinion on a "normal" 64bit sql box that is running nothing other than the database engine and possibly SSIS, would you configure the MIN Server memory as well, and at what level/ratio to the Max Server Memory or total memory?

    Subject: Re: MinMemory settings?
    Posted by: Jonathan Kehayias (view profile)
    Posted on: Friday, December 16, 2011 at 1:04 PM
    Message: Hey David,

    The 'min server memory' sp_configure doesn't make the instance grab all the memory configured right away. The instance still goes through a normal memory ramp up and once the 'min server memory' configured value is passed for the buffer pool, it will not shrink down below that size. If the server is dedicated to a single SQL Server instance setting 'min server memory' usually has no effect at all, unless the system experiences memory pressure and SQLOS starts to back off the buffer pool size. I only places I would consider using this option are; multi-instance failover clusters where it is possible for two instances to failover to one of the nodes, VMs where you want to limit ballooning with memory overcommit, or on a server where applications outside of SQL Server can commit large amounts of memory.

    Subject: LPIM bun fight
    Posted by: Perry Whittle (not signed in)
    Posted on: Saturday, December 17, 2011 at 3:08 PM
    Message: Interesting post, and I think this leads back to the even bigger bone of contention surrounding the setting of Min and Max memory to the same value. I've no doubt these debates will go on for some time

    Regards
    Perry Whittle

    Subject: Great article!
    Posted by: Alex Friedman (not signed in)
    Posted on: Monday, December 19, 2011 at 3:05 AM
    Message: Very interesting and informative. Thanks!

    Subject: no more debate
    Posted by: Jason (view profile)
    Posted on: Wednesday, December 21, 2011 at 9:47 AM
    Message: Excellent write-up. No more debate from here nowon. For those none-believer, please set up your own experiments and see it yourself. I have experienced and fixed the described memory scenario. SQL Server will drop dead (non-responding) for a while until memory is built backup. Imagine paging file is on hard-disk that is xxx times slower accessing speed than RAM.
    I have presented the default topic at SQLSaturday#57 Jan. 2011. The conclusion is exactly the same as Jonathan has illustrated.

    Regards,
    Jason
    http://dbace.us

    Subject: about default
    Posted by: Jason (view profile)
    Posted on: Wednesday, December 21, 2011 at 10:02 AM
    Message: By the way, about default, we have min/max parameters to set up that allows we control dedicated memory to SQL Server for steady performance. I size up OS, IIS, other components such as SSRS, and mem-to-leave memory (OLEDB, Oracle OLEDB driver etc.), say 25 GB. I will take total physical memory, 128 GB - 25 GB = 103 GB, grant 103 GB to Max-memory parameter. We are set for good. Monitoring, running and slightly re-adjusting occasionally. See my SQLPASS presentation.

    Jason
    http://dbace.us

    Subject: I think you two are saying the same thing
    Posted by: Jason (view profile)
    Posted on: Wednesday, December 21, 2011 at 11:02 AM
    Message: LPIM is granted from global policy to the user account running database engine. This can not be set as default from out-of-box SQL Server installation. It is a choice by DBA. about the out-of-box install default parameter min/max memory, Microsoft will not know how much available memory you actually have. It is up to DBA to set it. So what does it mean default? (out-of-box SQL Server install default?) or (config value DBA choose to run on as defaulty?) In this case, both Brent and Jonathan are correct. I have presented this topic at Houston SQLPASS June 2010.

    Jason
    http://dbace.us

    Subject: Seen it, done it
    Posted by: Jason (view profile)
    Posted on: Wednesday, December 21, 2011 at 12:58 PM
    Message: I had experienced the scenario Brent mentioned once. Someone from web server (host A) invoked w3wp.exe to run away with 22 GB memory usage on database server (host B). My guess is it was invoked via reporting service API. 22 GB RAM and possibly plus paging files consumed all remaining physical memory on this host. Luckily max memory and LPIM is set on my database server. Database is just running fine. I had to kill the run-away w3wp.exe. I had experienced Jonathan's scenarios many times. If LPIM not set, (although max memory is set), when windows is under memory pressure, it triggers SQL Server to release memory (not just a bit, a lot. In my case near 100 GB), SQL Server experiences a big drop of memory. The server is suddenly not responding for a long time. Tons of people run to DBAs' office. Phone is rining offhook. CIO demands a report.

    Jason
    http://dbace.us

    Subject: Agree both Kehayias and Ozar
    Posted by: SortaNewbie DBA (not signed in)
    Posted on: Wednesday, December 21, 2011 at 1:41 PM
    Message: The answer is really it depends on your enevironment and you have play around with different memory configurstions. I'm currently installating 2008 R2 Enterprise on a VMWare VM and as sool as the instance started, I was getting muliple "A significant part of sql server process memory has been paged out. This may result in a performance degradation. ", even with min configured for 50% of the RAM installed on the VM and 1GB left over for a OS. I just got this VM two days ago. I got the min/max reccommedations following Ozar's checklist, http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/. However, in my installation notes, I put a caveat, "if you get multiple memory paged out messages in the log, enable LPIM".

    So now I'm going to run Quest Benchmark factory to see what else what else I can make squeal.

    So thanks to both MVPS, plus Berry, Randal, and others!

    Subject: Apologizing for the Horrible Grammar and Spelling in My Previous Post
    Posted by: SortaNewbie DBA (not signed in)
    Posted on: Wednesday, December 21, 2011 at 1:45 PM
    Message: :(

    Subject: hum, Gramma ?
    Posted by: Anonymous (not signed in)
    Posted on: Wednesday, December 21, 2011 at 2:13 PM
    Message: Hehehe.. I know nothing about Russian grammar. Just ask Rick Perry about his Grammar: replied: she is fine, he is fine too.
    I guess there is no rules that says you have to post with British English or Australian Engilish or US English or Spanglish.
    :)

    Subject: Jonathan is ahead
    Posted by: Jason (view profile)
    Posted on: Wednesday, December 21, 2011 at 3:24 PM
    Message: To me, Jonathan is ahead at this round. For one thing, Brent (Czar) moderates the feedback on his site (how does he have so much time (like I do too)). For the second thing, Jonathan's arguments match my presentation at SQLPASS Houston, June 2010. As a DBA, the first priority to me is database has to work well, not to be brought down. Jonathan did an excellent job writing out all arguments. I think this article marks the end of debate. Default config or not, it is up to you. Someone prefers to suffer one way or another despite the story is told, it is nobody else's business. Anybody who wants to argue more, I strongly recommend s/he read this article first, along with Jonathan's free ebook.

    Jason
    http://dbace.us
    Happy Holidays to all.

    Subject: LPIM recommended with Hyper-V HVDM
    Posted by: mssqldude (not signed in)
    Posted on: Wednesday, December 28, 2011 at 10:40 PM
    Message: I read the link to Brent's article and it is from 2008. However, in regards to virtualization, Hyper-V uses dynamic memory instead of VMWare's style of RAM over-commit, so Microsoft actually recommends LPIM with Hyper-V: http://msdn.microsoft.com/en-us/library/hh372970.aspx, while VMWare generally does not. Just wanted to point out that distinction in hypervisor best practices. Love the SQL Server religious debates!! :) Br, MSSQLDUDE

    Subject: Great post
    Posted by: Anonymous (not signed in)
    Posted on: Thursday, December 29, 2011 at 6:27 AM
    Message: Excellent post, so thanks for this.

    I'm kinda with Brent though, I think there are so many scenarios to consider that for me it can't be a true default........yet.

    Thanks again.

    Subject: Great Summary
    Posted by: Paul White NZ (view profile)
    Posted on: Tuesday, January 03, 2012 at 7:23 AM
    Message: "I recommend that Lock Pages in Memory be used as a default configuration option on all 64-bit instances of SQL Server, unless you have a good reason not to..."

    Can't argue with that. Excellent article, Jonathan.

    Paul

    Subject: Re: LPIM recommended with Hyper-V HVDM
    Posted by: Jonathan Kehayias (view profile)
    Posted on: Tuesday, January 03, 2012 at 1:06 PM
    Message: mssqldude,

    The VMware docs don't say that Lock Pages in Memory is not a recommended setting, that is the recommendation Brent and I make with environments that are subject to being ballooned for stability. If you follow the VMware recommendations for using LPIM, you would have a reservation, as recommended in the article and ballooning isn't going to cause a problem for the VM, but you've reduce some of the environmental flexibility that VM administrators like to have by doing this.

    The VMware SQL on VMware whitepaper from March 2009 states that if you use LPIM, set a reservation to prevent ballooning problems (http://www.vmware.com/files/pdf/sql_server_virt_bp.pdf).

    The VMware Scalability Whitepaper from May of 2009 talks about the performance gains of using Large Pages which requires LPIM (http://www.vmware.com/files/pdf/perf_vsphere_sql_scalability.pdf).

    The VMware SQL Best Practices Whitepaper from 2010 states both of these items (http://www.vmware.com/files/pdf/sql_server_best_practices_guide.pdf).

    Cheers

    Subject: Re: LPIM recommended with Hyper-V HVDM
    Posted by: mkromer@microsoft.com (view profile)
    Posted on: Monday, January 09, 2012 at 8:26 AM
    Message: Hi Jonathan,

    Completely makes sense. I guess what I meant was that I typically use the SQL Server on VMWare Best Practices Guide from VMWare and it does not literally recommend using LPIM, whereas the Hyper-V guide that was recently published for utilizing Dynamic Memory in Hyper-V (Hyper-V's way to do similar to over-commit) actually explicitly recommends using LPIM to get better performance.

    I always read the VMWare guide as saying essentially well, IF you use LPIM, then make sure that you: "set the VM's reservations to match the amount of memory you set in the virtual machine configuration. This setting can interfere with the ESX balloon driver."

    Probably just my lack of understanding VMWare that led me to see that as not a direct recommendation to use LPIM and sort of scared me off.

    Subject: What about Windows 2012 and SQL 2012?
    Posted by: SirBoo (view profile)
    Posted on: Tuesday, April 16, 2013 at 5:44 AM
    Message: What setting is recommended on Windows 2012 with SQL 2012?

    Thanks!


     

    Phil Factor
    Searching for Strings in SQL Server Databases

    Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

     View the blog

    Top Rated

    Searching for Strings in SQL Server Databases
     Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

    The SQL Server Sqlio Utility
     If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

    The PoSh DBA - Reading and Filtering Errors
     DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

    MySQL Compare: The Manual That Time Forgot, Part 1
     Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

    Highway to Database Recovery
     Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

    Most Viewed

    Beginning SQL Server 2005 Reporting Services Part 1
     Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

    Ten Common Database Design Mistakes
     If database design is done right, then the development, deployment and subsequent performance in... Read more...

    SQL Server Index Basics
     Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

    Reading and Writing Files in SQL Server using T-SQL
     SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

    Concatenating Row Values in Transact-SQL
     It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

    Why Join

    Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.