8

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.

  • 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 Sep 8 '15 at 19:46
  • A really good blog post on VMware CPU Hot Plug vNUMA Effects on SQL Server by Jonathan Kehayias – Kin Shah Sep 9 '15 at 2:15
  • 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 Sep 9 '15 at 14:24
  • @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 Sep 9 '15 at 14:58
15

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.

  • Let us not forget the importance of dedicated and independent physical disks as well; one each for Data, Logs, and Tempdb. :) – Chiramisu Oct 27 '17 at 4:24
13

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):

11

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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