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.
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.
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:
57 Comments. Leave new
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
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:
https://www.brentozar.com/archive/2011/12/consulting-lines-pilot-dog/
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).
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.
No Worries Thanks Brent.
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.
That’s what I thought. Now I just need to prove why we need it (ie, how does it fix an existing problem).
Thanks!
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:
https://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?
Hi, Steve. Great catch – I actually updated my recommendations to 4GB and for some reason didn’t get it updated on the setup checklist. Thanks!
No problem. Thanks for the quick response!
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)’
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.
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’?
Smt – if SQL Server performance is important to you, then the first change to make is to move the web server onto a separate VM.
Brent,
Thank you – this solved our problem. We have the “value meal” setup for a TFS installation… SQL Server, SSAS, SSRS all running on the same VM. And just like you described, when SSAS spun up, the entire system slowed to a crawl. We had both min and max memory set to 11GB (on a 16GB RAM 64-bit system). Changing min/max to 1GB/8GB worked like a charm.
Also, great explanation of the VMware Balloon driver. Now I get how it works and now I understand what our IT guy was trying to tell us. Thanks!
Hi Brent:
How about when there are 2 instances on the same server. How do you suggest memory is managed in that case?
I have a VMware virtual machine with 24 GB of memory. The memory reservation on the machine is set to 18432 MB. When I had a single instance running on it I set the minimum memory to 14336 MB and the maximum to 20480 MB. Now I have a second instance and I have the challenge to configure the memory again. In this case, what do you recommend?
Thanks for your help.
I don’t ever recommend multiple instances in one VM.
Interesting. I have a consultant that is installing an application that will be using both instances and his recommendation is to use two instances for the application. He claims to have deployed the application in this way LOTS of times. He doesn’t have a memory allocation recommendation other than the 75% VM reservation. I’m not a super DBA but I have always preferred to place a single instance on any server, physical or virtual, mostly for convenience and not really seeing the point of multiple instances without a certain need. What kind of issues arise from multiple instances on a server other than the obvious memory contention issues?
Great question! Check out this training video to learn more: https://www.brentozar.com/archive/2011/09/how-plan-sql-server-virtualization-project/
What if I need multiple collations for databases less demanding applications?
Sure, run those in different VMs.
Thanks Brent Ozar, but when I was reading in:
http://www.vmware.com/files/pdf/solutions/SQL_Server_on_VMware-Best_Practices_Guide.pdf
which is a document from VMWare about the best practices of virtualizing SQL Server, they said (page 23):
“Do not set memory limits for SQL Server virtual machines. Virtual machine memory allocation target
is subject to the virtual machine’s memory limit and reservation. vSphere offers features to enable
dynamic scalability of virtual machine memory. Setting memory limits can cause unexpected
swapping.”
Does this contradicts with what you said about memory in this article or I misunderstood it?
Abu – they’re referring to VMware memory limits on the VM itself, not SQL Server’s max memory setting.
Hi Brent,
What is your current recommendation regarding multiple SQL Server instances within a VMWare VM? Previously you noted that you don’t recommend it in general. Is this still the best practice with the very latest VMWare version and if so, why exactly? Imagine a potentially wide VM with large amount of memory reserved on a dedicated ESXi host for this VM, with 16 vCPUs. Would you install multiple instances of SQL2014 EE in this VM if memory could be distributed according to each workload or rather separate the workloads onto different VMs, with 1 instance per VM? What is the concern here, potential vNUMA issues, CPU/threading problems for example?
The main driver for instance separation is security.
Thanks for your help!
Viktor – I don’t recommend multiple instances in a VM because it makes patches, security, and outages more inconvenient.
Thanks Brent,
However, I can’t see how SQL Server will benefit from setting the SQL minimum memory. In your example, if the MAX memory is 28G, and in the case of disaster when ballooning leaves 24G, SQL Server will (most probably) take them all as its MAX memory is set to higher than 24G (28G), and with this, SQL Server will not leave the 4G (or 10%) for the OS and the other major services, and we will be in a memory pressure again.
Excuse me, but I really didn’t understand you when you said:
” If I still want to leave 4GB or 10% free, that means my min memory should be 20GB.”
Brandon – the VM has 32GB of RAM, and we said the reservation is 24GB. That means at any given time, the entire OS may only have 24GB of RAM available.
In that scenario, if I want to leave 4GB or 10% free, whichever is greater, then 24GB – 4GB = 20GB.
It seems my question was not clear…..
How I am leaving 4G (for the OS ++) when I’m setting the MIN memory?! … SQL Server will keep caching until it reaches the MAX memory (which is limited to 28G), and not until it reaches the min memory … I can’t understand how I will leave (not use) whatever GB when I’m setting the “min” memory! … again you said:
” If I still want to leave 4GB or 10% free, that means my min memory should be 20GB.” But SQL Server does not look to the min when it wants to “give”, it looks to the “max” … it looks to the “min” when it wants to keep .. aint?!
Brandon – SQL Server starts at zero, and caches pages until there’s no memory pressure, up to max memory.
However, if it’s got a higher amount of memory than min, and the server comes under memory pressure, then SQL Server will back down its memory usage to the min memory setting.
Hello Brent,
Thank you for the excellent, concise article. I am trying to incorporate your settings advice setting up a new SQL 2005 64-bit SP2 VM with ESXi 6 on top of Server 2003 R2 SP2 64-bit. I have allocated 8GB in the VM setup, and have all of it marked as reserved. The VM correctly shows 8GB available.
When I go to the SQL Server properties, Max memory is set to the SQL 32-bit default of 2147483647, and I cannot increase it. I can decrease it, but that sort of defeats my purpose 🙂 Is there some magic I’m missing?
Curtis — you know 2005 is out of support, right? Like, completely.
To answer your question: pay really close attention to that setting. It’s in MB. 2147483647 MB is 2147 TB. With 8 GB of RAM, you’d probably want a more conservative number, like 6144. That’s 6 GB.
Thank you for the reply. Believe me, I know it is out of support, as is the Server 2003 it runs on. Sometimes, though, circumstances beyond IT control dictate the environment, and all I can do is support it the best I can. At least I managed to get rid of all almost all the XP workstations this year, and this SQL server is actually an upgrade from SQL 2000 32-bit!
Never mind. After sleep, my brain engaged and I realized the setting was not 2GB, but 2PB, and I needed to seriously turn it down 🙁
Hello,
We have quite a Strange effect on pour systems… We have 2 set of servers. Both using VMware. One set has 128 gig memory that I maxed out à 120 and the tec guy allowed the Locked page in memory for sql server. We end up with 64 gig locked (didn’t found yet where that 64 gig comes up)…
In our second set, we only have 64 gig max that I maxed to 58 giga and we don’t have the locked page in memory.
We are in a high pressure environnement with a lot of transaction (we needed to remove the Durability as we had writelog wait…).
The first set is always in trouble and is working set memory is very low (around 300 meg).
The numbers are quite confusing and I ask myself if that lock page in memory does’nt comes into the way avoiding sql server to take more than 64 gig…
How is it that sql server cannot goes up more than the 64 gig as the max server memory is set to 120 ?
I saw Something interesting in an old article talking about that subject…
“Just as with 32bit systems, any memory allocated using the AWE API is not part of the working set and therefore cannot be paged to disk. Therefore it is considered “locked”.”
Is this possible that when SQL server comes to the 64 giga and the part of the buffer that is outside the max server memory cannot acces more memory because the lock page in memory and so is like SQL server is really limited to 64 gig ?
Thanks
Olivier – for questions, head on over to dba.stackexchange.com.
thanks, I will.
Anyway, I should have said it differently. Does this parameter have side effect as it appears ?
Olivier – go ahead and ask the question on Stack like I suggested. (I’m trying to politely teach you how to fish.) Thanks!
Hello Brent,
We Installed a new SS 2016 Standar/Win 2016, but SQL goes ahead and blocks the max memory that’s allocated to it (25GB), but only use 300MB.
Hi Elkin. OK, keep me posted.
Brent, Thank for your response, Do you know How can we fix that? we need that SQL use 25Gb not only 300MB.
Does all of this apply if the server is only running SSAS? I’m having difficulty finding advise when SSAS is the only SQL piece installed.
It’s also very confusing on how to even set some of these properties in SSMS. Without the relational DB installed in the server, many of the options are missing.
Nah, SSAS is a totally different animal. SQL Server Analysis Services is just “free” in the box with the SQL Server product, but it doesn’t really have anything to do with SQL Server other than the fact that they both store data. None of the management techniques apply.
Thanks for the quick reply. In light of what you said, do you have any guidance on where I should be learning about SSAS specifically? It’s a challenge to sort through the differences, as the lines are so blurred. I’ve done my best with web queries. I’m having trouble finding a source I trust enough to recommend any changes to our environment.
No, sorry, I work with the SQL Server engine, not SSAS. Your best bet there will be to pick up a book about SSAS.
The lines aren’t blurred – it’s a Venn diagram like this:
SQL Server engine O ….. O Analysis Services
The SQL Server engine has absolutely nothing to do with Analysis Services.
Hello, SSAS works differently as when it comes to memory. The parameters are either in % or in Ko (of all server memory) if you change it… There are the 3 limits with memory that you need to adjust.
Be aware that ssas has a nasty panic mode where it will kill all process/queries that takes too much memory when the limit are near… you should set the max memory limit like sql for other server (giving some amount to the system) and adapt the % limit to the number you chose as max. Then it should work properly…
[…] Server installation. Specifically, we don’t change the value for Maximum Server Memory. There is guidance from Brent Ozar, Microsoft Data Platform MVP, that suggests that you should set aside 4 GB of memory for the OS or 10%, whichever is greater for […]
[…] Server installation. Specifically, we don’t change the value for Maximum Server Memory. There is guidance from Brent Ozar, Microsoft Data Platform MVP, that suggests that you should set aside 4 GB of memory for the OS or 10%, whichever is greater for […]