MSSQLWIKI

Karthick P.K on SQL Server

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 Smile?

clip_image002

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 Smile?

clip_image004

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 Smile.

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

4 Responses to “SQL Server and VMware ballooning”

  1. 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.

  2. […] SQL Server and VMware ballooning […]

  3. 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 ?

  4. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: