current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite
3

My understanding is that one of the big benefits of using a VM is that you can share resources between the virtual machines on a host. So you can have a host with 120 CPUs and put 5 VMs on it with 32 CPUs each. The extra CPUs are "shared" between the VMs with the host assigning CPUs dynamically based on need. Same for memory.

I also understood that this is a big no-no for VMs that house SQL Servers but my VM admins disagree. Does anyone have any evidence or documentation one way or the other?

I don't know if it matters but we are using VMWare.

share|improve this question
2  
oddly enough, we just had an issue with random failovers on a virtualized availability group. we were oversubscribed on CPU with a ready time of ~8 seconds. the ag listener lost its cluster lease and would flop to the other HA node. the difficulty here is it doesn't present as CPU pressure on the guest, even though the error is generally associated with sustained CPU pressure. this leads to the biggest issue with virtualization - knowing what's going on and trusting others to do the same. –  swasheck 20 hours ago
    
A really good blog post on VMware CPU Hot Plug vNUMA Effects on SQL Server by Jonathan Kehayias –  Kin 14 hours ago
    
btw: It is not normally "The Same for memory". In most cases you share the cpu, but a s specific amount of memory is allocated to each VM, and this memory is not shared. Sharing cpu with a SQL Server might be OK depending on total cpu load for all your VM's, but sharing memory with a SQL Server would be bad in almost all cases –  MTilsted 2 hours ago
    
@MTilsted Sorry for the confusion. When I said "Same for memory" I meant specifically that it could be shared. When I was asking the question I realized that it might very well be different for CPUs and memory but that both were an option. –  Kenneth Fisher 1 hour ago

3 Answers 3

up vote 13 down vote accepted

What you're asking is, "Is it okay if we overcommit CPUs?"

It's okay until you start experiencing performance bottlenecks that are related to CPU consumption. The same answer holds true with network overcommitment as well - it's not like you're putting 5 separate 10Gb Ethernet cards in the host, and dedicating one card per VM. Virtualization is all about overcommitment, and walking the line between resource availability and resource requirements.

Generally speaking, you don't want to overcommit on memory, though, since SQL Server will actively use whatever memory you present to it. Rather than letting the VMs page to the host's disk, you're better off doing VM-by-VM analysis to figure out which VMs could live with less memory, and then configuring them with less memory to begin with.

share|improve this answer
up vote 10 down vote

The problem I see with having dynamic allocation of some resources is that it leads to unpredictable performance. Reporting query x had 32 CPUs available yesterday, and ran in 4 minutes, today it only has 24 and it took considerably longer. You can also see latency as the guest waits for other cores to become available.

Jonathan Kehayias gives some practical warnings about over-subscribing CPU and memory here (and frankly I trust his experience and advice more than a typical VM admin, no offense to them, but I suspect he has a lot more direct experience with the combination):

share|improve this answer
up vote 9 down vote

My understanding is that some level of CPU over-commitment may be perfectly OK, but that totally depends on the workload requirements of all the guests. Check out David Klee's blog for a lot of good VM information. Specifically http://www.davidklee.net/articles/sql-server-articles/cpu-overcommitment-and-its-impact-on-sql-server-performance-on-vmware/ for CPU over-commitment explanation and its impact on SQL Server.

Memory over-commitment is another animal entirely and in general, the VM host should not be over-committed on memory if it's running SQL Servers. Once again I will use David Klee as a reference. http://www.davidklee.net/2013/11/04/lock-pages-in-memory-in-sql-server-on-vmware-why-or-why-not/ where he explains the effects of memory over-commitment.

Hope this helps.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.