SQL Server and VMware ballooning
Posted by Karthick P.K on March 31, 2013
VMware and SQL Server performance
If you are running production SQL Server on VM-Ware double check if you have configured/disabled ballooning for the virtual machine in which SQL Server is running.
What is ballooning? Method by which VMware host can reclaim memory from the Virtual machines.
Is it really bad to give memory from Guest to Host ? My opinion is yes if you are running production SQL Server on VM-Ware.
Why I think it’s bad? SQL Server is memory intensive application and requires adequate memory for smooth running. If SQL Server doesn’t have adequate memory to run you see poor response time, Resource_semaphore/ Resource_semaphore_Query_compile waits, increased I/O, OOM errors, Non-Yield condition’s etc. In addition to all this when memory is reclaimed from virtual machines available memory in windows drops triggering windows to page out the working set of all the processes and you will face all side affects discussed in A significant part of SQL Server process memory has been paged out .
In worst case it is better not to give memory for SQL Server instead of give and take back. Remember Max server Memory is also a factor which will impact the generation of execution plan by the optimizer, So plan generated when you have X GB of max server memory may not be the right plan to use when you have Y GB actual memory after ballooning reclaimed memory from guest OS.
What if hypervisor runs low in physical memory? It gives a hint that you have a poor consolidation. You can pick up the other Virtual machines that are not hosting production SQL Servers (or SQL Servers) from same Hypervisor and tweak reservations (or) Increase the maximum memory that can be reclaimed when the hypervisor is under memory pressure.
What if I don’t disable ballooning for my production SQL Server? Ballooning can slowly take the memory from virtual machine in which SQL Server is hosted and can cause all the problems I mentioned above.
To make things confusing when you look at task manager you may not even realize that ballooning has reclaimed memory from Guest OS, Because total physical memory shown in performance tab includes the memory taken by ballooning driver.
How to identify > Look at the driver locked memory from RAMMAP sysinternals tool. (VM memory performance counters can also be used)
Some of the RAMMAP output captured in production SQL Servers can self-explain. Driver locked value would be few MB’s in normal systems, If the value is very high in VMware virtual machines then you can assume ballooning is reclaiming the remaining memory .
Below is output of RAMMAP from Virtual machine with 12 GB memory hosting SQL Server with max server memory capped to 8GB.
Driver locked is around 8GB. So the system is running with less than 4Gb of RAM and how much is for SQL 
?
Below is output of RAMMAP from Virtual machine with 24 GB memory hosting SQL Server with max server memory capped to 20GB.
Driver locked is around 16 GB. So the system is running with less than 8Gb of RAM and how much is for SQL ?
How to disable ballooning ? Refer http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1002586
You don’t agree ? I respect your view but my view is different .
Note: I have not recommended to disable ballooning in every virtual machines. I recommend to do it for your performance sensitive SQL Server and if you find your hypervisor is running low in memory revisit your consolidation (or) Configure other non critical virtual machines running on same host in such a way that hypervisor can reclaim memory from them when it is under low memory condition.
If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group
Thank you,
Karthick P.K |My Facebook Page |My Site| Blog space| Twitter
Disclaimer:
The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights
MSSQLWIKI 
Pedro said
Great Post Karthick,
I have a SQL 2008 x64 on VMWare (Server 2008 x64) with 12 GB of memory, but when i start the VM take almost 95% of memory, i try upgrade the memory to 16 but i have the same problem. I check the Resources tab on the VM and it that on 4GB but i change this value to Unlimited but i have this message “insufficient resources to satisfy configured failover level for vsphere ha”. I read in some posts the solution is active Unlimited option or dissabled the ballooning drive?
Regards.
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] SQL Server and VMware ballooning […]
Shashank said
Hi Karthick,
Thanks for the post, you said its not advisable to disable balooning driver on every Hypervisor. So can you please tell what steps can we take to check whether we should disable it or let it be enabled. I was more interested in proactive approach, i mean way I could decide before it causes OOM condition.
Plus is it supported by MS to run RAMMAP tool on production server ?
Pierre said
Hello, your post shows a slight misundestanding on how and when the balloon drivers works in Vmware (I don’t know about Hyperv-V implementation). Basically balloon drivers kick in when ESXi host has exhausted its physical RAM (beyond 94%) and VMs are still asking for more memory.
In this case balloon driver is a mean to ESXi to cooperate with OS to free RAM. A balloon driver is a program that request RAM from the OS and indicates to ESXi that the page he has a free and can be safely swapped. If the SQL Server memory is locked, RAM will not be given to balloon, simple as that.
What happens when balloon driver is disabled and memory pressure is too important ? ESXi will page to disk as much memory pages as necessary wether they belong to Windows, Notepad or SQL Server, and performances will go up in smoke, where a controlled method of saving RAM may have helped in a temporary memory contention.
There are very simple (and much better than disabling balloon) ways to protect SQL Server
First possibility reduce consolidation to consume less RAM under 94% physical RAM. In this config balloon NEVER happens.
Secondly, if reducing memeory pressure is not possible, create a 100% memory reservation for SQL Server VMs where physical memory is guaranteed to the VM, and cannot be used by any other VM under any circumstances.In this config balloon NEVER happens.
Third (best one in my opinion): make sure you have no physical resources overcommitment for critical VM like production databases. In this config balloon NEVER happens.
Tweaks like disabling the balloon drivers for VMs is useless and is bad advice, in my opinion.