Recently I had a client complain of chronic high CPU utilization. The performance of their SQL Server had degraded, and it appeared to be related to higher than normal CPU utilization in conjunction with symptoms of unresponsive user queries. The root cause was twofold—a third party hosting provider had overallocated virtual processors on the physical host where the virtual machine (VM) running SQL Server was residing, as well as a recent upgrade from a version of VMWare that was not patched for Spectre and Meltdown. The host had 16 physical cores and was hyperthreading (making it effectively 32 cores) until the hosting provider patched from VMWare 5.5 to a newer release (we believe 6.5) which was required for Meltdown and Spectre processor vulnerabilities. This patch disabled hyperthreading from the hypervisor to mitigate the security risk from speculative execution. Note, this patch is over a year old and a critical security risk; most software vendors (including VMWare) put this out as an immediate requirement after the announcement of the vulnerabilities.
Given this was a virtual machine, it shared a physical host with many other VMs; this is a very common configuration. However, this host was VERY overallocated. As mentioned above, there were 16 cores–however 61 additional vCPUs had been allocated to other machines. That’s 4.3 times the number of CPUs available for allocation. The screenshot below shows this singular Host, highlighting the vCPUs allocated.
So, uh, that’s a bad thing. Monica explains in detail why exactly it’s a bad thing, which is helpful when you’re trying to explain to the server admin why it’s a bad thing. CPU oversubscription can work for things like dev boxes or web servers, where they typically aren’t anywhere near 100% utilization. It does not work at all for busy database servers.
Why is Over-Allocating a VM Bad?
Why is is so important to allocate just the right amount of resources for your guest VM running SQL Server? We all know the problem with an undersized system; however, what about an oversized system? What problem could that be? Here are a few common issues to consider:
Over-allocating CPU causes poor resource utilization across all the guest VMs
Over-allocating memory unnecessarily increases memory contention and overhead on other guest VMs
Having more vCPUs assigned to the VM can have an impact on licensing.
There are some good points in the post, so check it out.
Both Hyper-V and VMware’s default controller emulates the LSI Logic SAS controller, because that’s what is built into Windows driver storage, and *just works* without having to do anything fancy.
But… it’s not necessarily there for speed. It’s there for compatibility so that you can boot up a VM without having to deal with extra drivers.
VMware created a driver a while back that comes with the VMware Tools package called the Paravirtual SCSI controller, and it gives a 10-30% bump in performance (depending on the speed of the underlying storage) because it’s built for speed from the beginning. It’s just not native to Windows, so I don’t personally feel comfortable using it for the C: drive controller unless required. You can change the controller type for these controllers, so we use it by default for non-OS SQL Server object drives.
Definitely worth the read.
Part 1 of Creating a SQL Server Test Lab On Your Workstation can be found HERE
In the second part of our series on creating a SQL Server test lab on your workstation, I’m going to look at creating a domain user, joining a SQL Server to our domain and connecting to that server using our domain account.
Read on to see how to do just that.
There are a lots of command to create or manipulate VM’s and I’m still only scratching the surface, but although I’m not a PS person, I have to admit that every time I want to do something, I find relatively easy to find a powershell command or a script for it, so I like it.
For instance, creating new virtual machines it’s a simple as one command
And that’s only the beginning, we can add the different virtual hardware like in the UI, Drives, Network Adapters and so on. And then configure memory, CPU and NUMA, etc…
This is the script which I’m more or less running to create my VM’s, this in particular will be a Hyper-V Host itself, so there are a couple of interesting settings I’ll tell you about later.
Click through for Raul’s script.
I’m constantly spinning up VMs and then blowing them away. Ok, using the Hyper-V GUI isn’t too bad but when I’m creating multiple machines it can be a bit of a pain.
So here’s the details on the script I’ve written, hopefully it could be of some use to you too.
Click through for the script; it’s ultimately just a few lines of code.
In a virtual data center CPU is spread across many guest VMs. This is one of the key drivers behind the effort to virtualize – CPU cores mostly sit unused. For example, we can take a host with maybe 48 cores and virtualize many machines that present logically with > 48 cores. The hypervisor can swap in and our cores as it needs based on what the guest VMs are doing. If the baseline for a guest VM is only 10% CPU usage then this is easy. However, when an intense application like SQL Server is virtualized it must have CPU available otherwise performance will suffer noticeably.
Generally for CPU on a guest VM:
Reservations on CPU are not often possible but consider them if you data center allows for it.
You want more cores than sockets. So if you are aiming for 8 cores you want something like 2 sockets with 4 cores each instead of 8 sockets with 1 core each.
If priority can be given to the SQL VM for CPU then change the Shares Resource Allocation from normal to high.
Click through for more helpful hints.
You wants 1×6 (one socket, 6 cores) because standard edition will only use the first 4 sockets in a server (up to 16 cores combined). There’s no getting around that.
From a NUMA perspective as long a vNUMA at the Hypervisor is disabled then it doesn’t matter as SQL Server standard edition isn’t NUMA aware (NUMA awareness is an Enterprise Edition feature).
Read on for a more nuanced answer when it comes to Enterprise Edition.
But what happens when the VM is configured with less vCPUs than the core count of the physical CPU package and CPU Hot-Add is enabled? Will there be performance impact? And the answer is no. The VPD configured for the VM fits inside a NUMA node, and thus the CPU scheduler and the NUMA scheduler optimizes memory operations. It’s all about memory locality. Let’s make use of some application workload test to determine the behavior of the VMkernel CPU scheduling.
For this test, I’ve installed DVD Store 3.0 and ran some test loads on the MS-SQL server. To determine the baseline, I’ve logged in the ESXi host via an SSH session and executed the command:
sched-stats -t numa-pnode. This command shows the CPU and memory configuration of each NUMA node in the system. This screenshot shows that the system is only running the ESXi operating system. Hardly any memory is consumed. TotalMem indicates the total amount of physical memory in the NUMA node in kb. FreeMem indicates the amount of free physical memory in the NUMA node in kb.
I am proud to announce that we contributed to the latest revision of the Microsoft SQL Server on VMware best practices guide, freely available at this address. This document outlines some of the common VM-level tweaks and adjustments that are made when running enterprise SQL Server VMs on VMware platforms. This guide is considered a must-read if you manage these sorts of SQL Servers, which cannot be treated as general purpose virtual machines.
This guide was recently updated for vSphere 6.5, and we consider it an absolute must for your enterprise management library!
If you manage SQL Server instances on VMware, it’s definitely worth the read.