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: