Nowadays everyone virtualizes. SQL Server is no exception. VMware is the most common hypervisor by far (sorry Hyper-V) so it is very beneficial for DBAs to know their way around vSphere, understand how to optimally configure SQL Server and VMware to work together, and know the red flags to watch for performance.

This is part 2 of a 3 part series about SQL Server Environmental Diagnostics. Check out Part 1 and Part 2 as well.

VMware General Configurations for SQL Server

Read the following VMware KBs and follow the best practices. Common virtual machine mishaps such as CPU constraints, memory overcommitment, storage latency, network latency, and other tips are discussed.

Always remember to install the VMware guest machine additions!

Troubleshooting Guidelines:

vSphere Key Performance Metrics

When creating the guest VM remember to only create and connect with you need.

Here are some other key points:

Disconnect any unused or unnecessary devices such as COM ports, CD-ROM drives, USB adapters if you don’t need them.
Disable screen savers and Windows animations (drains CPU)
Please only 1 SQL Instance per VM!
Install SQL Server just like on a physical machine

CPU Issues

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.

One of the red flags you’ll have to be on guard for is CPU Ready %. This is a measure of the time a CPU core was scheduled and ready to perform a task but had to wait for a core to be free from the hypervisor to run. Very bad things happen here like SOS_SCHEDULER_YIELD waits and slow performance.

CPU Ready

CPU Ready is a measure of the time the processor core is in a ready state on the guest VM. It represents a wait type for the processor from the hypervisor when balancing among different machines.

Overcommitting the VM Host CPU to Guest VMs can cause more trouble than benefit. The hypervisor must keep track of CPUs and context switch between them across all guest VMs. Try to “Right-Size” the guest machines rather than over commit.

Identifying high CPU Ready

There are a few ways to know if CPU Ready % is a problem.

The command “esxtop” can be run from the ESX host to get general statistics about the VM host.
Another way to measure CPU Ready % is the Performance tab on the guest VM.
Use advanced chart options to filter CPU to weekly and get the usage and ready in summation counters.

We want to see waits in CPU Ready to be below 10,000ms. A range of 5000-8000ms should be as high as they get.

You will start to notice performance suffering when CPU Ready is at or above 3%.

This is how to calculate the CPU Ready % from the above measures:

Fixing high CPU Ready

The way to remediate high CPU Ready % is to reduce the number of cpu cores presented to guest vms. Reducing the number of processing cores will reduce the chance of receiving a CPU Ready wait type in vSphere. This will improve processing and performance.

NOTE: often times the guest VM hosting SQL Server for an application is not the only machine in the host machine pool. Be mindful of the other guest VMs on the host and their distribution of cpu cores.

Memory Ballooning

Memory ballooning is a process by the hypervisor to reclaim memory as needed across the guest VMs from a host. If any is detected it has to be immediately remediated.

Identifying Memory Ballooning

Memory ballooning can be detected at the guest VM performance tab. Check the any of the time options and choose the usage and balloon values. Any non-zero value is bad.

Fixing Memory Ballooning

Resolving memory ballooning calls for additional memory to be allocated to the guest VM suffering from this. Either unused memory or a reshuffling of allocated memory from other guest VMs to the affected machine. Additional memory will help here too!

If memory reservations are in place make sure there is enough memory in the lower bound for the guest machine to perform without excessive paging. Also make sure the VM host isn’t stressed for memory and the hypervisor doesn’t have to reclaim memory to service other guests.

If memory is overcommitted then either increase memory to the host OR reduce memory to the guest VMs
Run esxtop, type “m” for memory, type “f” for fields, select “j” for memory ballooning statistics, look at MCTLSZ value (amount of memory reclaimed by balloon driver)
Type “f” for field, select letter for Memory Swap Statistics (SWAP STATS), review SWCUR value (current swap usage)

Disk

Here are some points about disk:

Microsoft recommends that SQL disk block size be set to 64kb for optimal IOPS.
The SQL VM should have Independent Persistent virtual disk mode

PVSCSI – Paravirtual SCSI Adapter

When possible use the VMware Paravirtual SCCI Adaptors (PVSCSI) for disks. PVSCSI adapters are high performance storage adapters made for applications like SQL Server i.e. high reads and writes like in a SAN. This is a preferable option to use. If you are dividing up disks to put data files, transaction logs, tempdb then use a seperate PVSCSI adapter for those disks too.

Configuring Disks to Use VMware Paravirtual SCSI (PVSCSI) Adaptors

Concluding Thoughts

SQL Server on VMware works great if you know the best practices. Optimally configuring VMware and SQL Server will yield better performance and easier debugging. If you don’t have access to vSphere then download the client that your company is on and talk to the team responsible for granting permission to vSphere. It is worth the trouble. Don’t let virtualization issues get between you and taking care of your databases!

NOTE: David Klee has a blog with many great posts about virtualizing SQL Server. Give them a read.

Like what you are reading? Please subscribe!