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.

Community
  • 1
Kenneth Fisher
  • 24,307
  • 13
  • 63
  • 116

3 Answers3

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.

Brent Ozar
  • 43,325
  • 51
  • 233
  • 390
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):

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
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.

cjsommer
  • 336
  • 2
  • 8