Blog

SQL Server has two settings that kinda-sorta govern how much memory we’ll use to cache data: min server memory and max server memory.  I explain the basics of how they work in my Sysadmin’s Guide to SQL Server Memory, but things are a little different under virtualization.

VMware does a great job of sharing memory between different virtual machines, but to do that, sometimes it has to steal memory from one VM to take care of another.  Just because we gave 16GB of memory to our VM doesn’t mean the memory’s always there.  If a host crashes and we suddenly need to boot up a bunch more guests on our existing host, we might pull some memory away from the other guests temporarily.  If our company’s really cheap, we just might have never bought enough memory to begin with, and the memory might be stolen permanently.

Some of my restaurant choices are less satisfying than others.

To work around that, VMware admins can set a reservation for any guest’s memory.  It works like a reservation for a table in a restaurant – we’re guaranteeing that a corner table memory will be available whenever the virtual server needs it.  By default, guests don’t have reservations – they just walk up and try to take whatever they need at the time.  That works really well for most applications, but not for SQL Server.  SQL Server starts at near-zero memory used, and then gradually caches more and more data as queries request it.  Unlike most apps, SQL Server’s memory needs don’t go back down.  It’s like that guy who keeps going to the buffet over and over and claiming all the food for himself.

When we build new virtual machines, we need to come up with three numbers:

  • The guest’s memory – this is the amount of memory the guest thinks it has when it starts up.  Let’s say we’re building a virtual machine with 32GB of memory.
  • SQL Server’s max memory – I like to set this to leave 4GB of memory for the OS or 10%, whichever is greater.  In this case, we’d set SQL’s max memory at 28GB, which would leave 4GB free for the OS.
  • The VMware reservation – the lowest amount of memory the guest will have.  Ideally, this is 100% of the guest’s memory, but that’s not always practical. If a host crashes, I’d rather be able to boot up all my guests with less memory than not be able to boot them up at all.  For SQL Server, I generally set my reservations at 75% of the guest memory – in this case, 24GB.

So now we have an interesting problem: in the event of a disaster, VMware’s balloon driver may fire up and claim 25% of the memory, leaving just 24GB total for the guest.  This will come as an ugly surprise for SQL Server because he was humming along using 28GB of memory (our max).

That’s where SQL Server’s min memory comes into play.  I have to set the min memory in a way that accommodates my reservation.  If my reservation is only 24GB, that means the balloon driver might pipe up and steal 8GB of my memory at any time.  If I still want to leave 4GB or 10% free, that means my min memory should be 20GB.

Excel – When you care enough to draw the very least.

The max memory number doesn’t change – but suddenly we need to pay more attention to our min server memory number.  It’s completely okay to set that number even lower as long as you’re okay with reduced performance.  For example, if this server is a value meal that also hosts SSAS/SSIS/SSRS, you’ll need to set min memory much lower to let those other apps get their jobs done.

If the VMware team refuses to set a reservation, you can’t fake your way around it by setting a high min server memory number.  When things start swapping to disk, SQL Server is going to run slower – even if it’s not the one paging to disk.  When the OS ain’t happy, nobody’s happy.

Wanna learn more? Check out our VMware, SANs, and Hardware training videos. It’s a 5-hour training video series explaining how to buy the right hardware, configure it, and set up SQL Server for the best performance and reliability. Here’s a preview:

Buy it now.

↑ Back to top
  1. Great article Brent! I came across the VMware balloon driver when we ran into serious performance degradation. I did a blog post on it;

    http://sqlserver365.blogspot.co.uk/2012/01/dont-be-balloon.html

    Cheers

    Chris

  2. Once the balloon driver engages and consumes the 8GB in the example, SQLOS will reduce memory usage to return memory to Windows as it sets the low memory resource notification flag on but as soon as Windows turns it back off, which is when it passes ~90-150MB of available memory, SQLOS will grow it’s target in an attempt to get back to ‘max server memory’. The ‘min server memory’ only would take effect if after being ballooned you had another process in Windows that was consuming memory actively creating further memory pressure. It’s not going to ensure you have 4GB or 10% free after ballooning occurs.

    • Jonathan,

      What if i set LPIM for my sql server account and then ballooning does happen. If SQLOS was notified the low memory signal then how it can give back its memory to windows again. Please do correct me if i am wrong.

      -Aditya

      • LPIM just stops Windows from being able to force trim the memory allocated by AllocateUserPhysicalPages, it doesn’t prevent SQLOS from reducing it’s memory usage and returning memory to Windows. Only if Large Pages (enabled with TF 834 on Enterprise Edition only) are being used is the buffer pool size non-dynamic. Neither Brent nor I would recommend using LPIM as a default in a VM that could be ballooned by the balloon driver because it can cause instability if the balloon driver consumes memory faster than SQLOS responds and the OS encounters a hard OOM condition. See his blog post:

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

  3. Brent, thanks for the detail. You’ve mentioned in past presentations that SQL Server and VMware memory settings should be set “in concert” with each other. This post makes it perfectly clear how to do it (with the help of Excel, of course).

  4. Hey Brent Great Article. I recently posted a question on dba exchange (yet to be explained) regarding why sql takes memory more slowly under virtualization I was wondering if you had any insight as to what causes this ?

    • David – I haven’t seen that myself, but I’m really big on testing. If you were going to test that hypothesis (that SQL takes memory more slowly under virtualization), how would you go about doing that? Do you think there’d be a way you could test it under a controlled environment by setting up two identical instances and then doing two identical queries that would cause the buffer pool to grow at the same rate?

      • That pretty much what I did. I had a physical box with workload x it took max memory within 2 days. Under Vmware 5.1 as the only guest on the box with identical workload it takes weeks to get to half of the max memory. Its strange.

        • David – sorry I’m not giving quite the answer you were looking for, but I was trying to get you to come up with the answer. ;-) I’ll be a little more direct.

          Instead of relying on days of workload, simply run a SELECT * FROM Table query, using a table that is sized larger than memory. The buffer pool should pop right up to full as the data is scanned. You can then verify the hypothesis in a matter of minutes, and it should be easily reproducible. From there, you can start doing a little more close examination.

  5. No Worries Thanks Brent.

  6. Pingback: SQL Server Thumbnail Metrics – OS Memory « Voice of the DBA

  7. Pingback: Something for the Weekend - SQL Server Links 30/11/12

  8. I sent your article to our VM people and they are open to setting reservations for us if I make a good enough argument for it. Is there a down side for us to setting this? Especially for lower memory servers.

    • Kristy – the downside is that if you don’t have enough physical memory backing the VMware hosts, the other guests could come under memory pressure.

  9. Hi Brent. I am hoping you can clear something up for me. You mentioned the following in this article regarding the Max Memory setting:

    “I like to set this to leave 4GB of memory for the OS or 10%, whichever is greater.”

    However in the post for SQL 2005 – 2012 setup you suggested something different:

    ” I like to leave 10% of the memory free, or 2GB, whichever is larger.”

    This is the post:
    http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/

    What setting do you prefer to start out with, 2GB or 4GB?

  10. Hi Brent,
    I have a 2 node cluster running SQL Server 2012 Enterprise Edition on Windows 2012. They are both VMs running on VMWare 5.1. I have set my max server memory and min server memory to what you have suggested in your article.

    So we have 16 gb RAM available and I have set the max to 12 and the min to 8. The VM has a reservations of 12. But what I’m seeing in the SQL Server is that total server memory used is about 14 gigs. I am not sure why that is the case since I set the max to 12? Shouldn’t it use no more than 12 by sql server? And once it holds on to the 14 gb, does it never let go of that?

    Thanks!
    Kaitlyn

    • Kaitlyn – that’s an interesting question. Where are you seeing that SQL Server’s total memory used is about 14GB?

      • I ran this query:
        SELECT object_name, counter_name, cntr_value AS ‘Total Server Memory (KB)’
        FROM sys.dm_os_performance_counters
        WHERE counter_name = ‘Total Server Memory (KB)’

  11. So, I get the gist of what you’re saying here. Ballooning is bad and is well known to avoid it. But there are other memory technologies at play that aren’t mentioned that I’m curious about.

    When I look at the “Active” memory usage on a SQL 2005/2012 server with 32 gigs of RAM set as the minimum, VMware’s Active memory shows a much lower number – somewhere around 8 gigs under load.

    I think the part that you’re missing is Transparent Page Sharing and Memory Compression technologies from VMware. You can’t think of memory on a VM like a physical box. By setting your minimum at such a high number, you’re wasting a good deal of valuable RAM.

    What I generally suggest is that

    1.) You avoid memory reservations and just don’t over commit RAM. That would take care of your Ballooning issue while not reserving memory you may not be using.

    2.) Look at all the monitoring you have available. Windows perfmon and task manager only gives you what VMware is telling it. In actuality, behind the scenes compression and dedupe (TPS) is working to provide you more resources than a physical server can provide.

    Just my 2 cents….

    • John – Transparent Page Sharing only works if the pages are the same. For any two SQL Servers, it’s extremely unlikely that they’re hosting the same database pages in memory at the same time. After all, if you’re hosting the same database on two servers, well, what’s the point of that?

      There’s actually something interesting YOU’RE missing – the “Active” memory on VMware is a sampling of which pages have been accessed recently on the server. It literally does mean “Active” – whereas SQL Server uses memory to cache data that may not have been accessed in the last couple of minutes.

      Hope that helps clearing things up!
      Brent

      • Thanks Brent for the comments…

        Memory pages are only 4k so I think that out of the multi-gigs of RAM being consumed, there’s a lot of pages that match. Not to mention the SQL executables, Windows OS and related apps.

        I’m very well aware of the Active memory being a sampling of touched memory over the prior 20 seconds. That’s why I said that you need to look at all the monitoring you have available.

        I think you focused on just the SQL side of this discussion so I was simply implying that there’s many other factors to consider and you shouldn’t simply change reservations as a way to prevent ballooning.

  12. Hi Brent,
    On my virtual SQL box there is web server also installed. When i went to ‘Resource Monitor’ saw w3wp.ext was on the top and taking more than 1.25 GB (commit) and sqlserv.ext was only 350 MB.
    Server has 16 GB total memory, Max Server Memory in sql setting was 12 GB. What a saw ‘Physical Memory’ was constantly 95%.
    So I did lower Max Server Memory to 10 GB for Sql and 6 GB for other services. Now Physical Memory’ in Task Manager went down to 85%!!! Do you agree the change or there is better way to do it?
    And if you would, how much Min Server Memory do u recommend in this case? I have default 0 right now. Should i also ‘Lock pages in memory’?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php