Feng Shui, VMWare, and SQL Server – Finding Balance

Feng Shui is the practice of arranging pieces in living spaces to create balance with the natural world.

VMWare vSphere virtualization has grown since its introduction in 1998, to being the top virtualization technology in use by companies today, holding 23% of the market.

Microsoft’s SQL Server product, first released in 1988, has increased its market share to where it also holds the largest market share of industry use at almost 17%.

All too often, when working with a client having SQL Server performance issues, they are using VMWare for virtualization. The two products, by nature, are opposites. VMWare wants to share physical resources, while SQL Server expects to have CPU, memory, and disk resources immediately.

This conflict can be avoided with proper allocation of resources and configuration of the VMware infrastructure and the SQL Server Virtual machine, finding the balance between the products.

Server consolidation with Virtualization

Virtualization has huge benefits for data centers. By being able to consolidate servers into virtual machines (VMs), companies have been able to reduce hardware costs considerably. You can effectively turn one physical server into five or more servers because in general, computers are essentially idle.

Unfortunately, this is not a free lunch. At some point in time, all the VMs are going to be requesting for resources at the same time, and there will be delays. This works fine with file server, web servers, etc., where a delay is acceptable.

SQL Server does not respond well with delays.

SQL Server and the need for speed

The primary goal for SQL Server is speed, speed, speed. And the speed limiting factor on SQL Server is moving data between the SQL Engine and disk.

Are you sure you are getting the proper performance from your storage? Did anyone confirm the disk throughput on a VM in the VMWare cluster?

All too often best practice details, like having Jumbo Frames enabled in the SAN network infrastructure, are missed and you are now sending eight packets instead of one jumbo packet.

You need to confirm that your SAN infrastructure is delivering the performance the vendor claims it is providing. Just running a simple benchmark tool like CrystalDiskMark can keep them honest.

It’s all about the Drivers

VMWare provides a basic VM template to be used to create VMs. It’s configured to use the Intel e1000 Network Driver and the LSI Logic SAS Disk Driver.

This was intended to be a simple example, with your VMWare administrator creating new VM templates using the VMware Paravirtual drivers, optimized for throughput and reduced CPU usage in the VM.

Unfortunately, the VMWare Administrators almost never do that.

Replacing the LSI Logic SAS driver with the Paravirtual SCSI (PVSCSI) driver will provide up to 10% more IOPs with up to 20% less CPU usage without tuning the queue depth. With tuning in the Windows Registry, 20% more IOPs and 20% less CPU usage as compared to the LSI Logic SAS driver.

Similar improvements can be gained with replacing the Intel e1000 Network Driver with VMWare VMXNET 3 Paravirtual Network Driver.

A simple driver change will reduce the CPU demands and improve disk and network throughput on ALL the VMs being hosted.

Less is More

Over-allocation of VM vCPU is one of the most common mistakes that occur in SQL VM settings. A VM is not a physical server. You need to configure it with the least number of vCPUs needed to perform the work. This is known as Right-sizing. Right-sizing is critical for performance with a VM, and it is not the same as sizing a physical server.

You must keep in mind that your VM is going to be competing for CPU resources with the other VMs on the environment unless you are willing to have sufficient hardware to dedicate and reserve the CPU and memory resources for the SQL VM.

An important metric to keep an eye on is CPU Ready. This metric tracks the time that a VM is waiting for CPU from the ESXi host. Ideally this needs to be as close to zero as possible. Anything over 5% can introduce SQL wait events that are detrimental to performance, and unexplainable from within SQL Server.

In Summary

Balance is important. Without balance, it is far too easy for to be off-center and not be able to perform at your best. This holds true with VMWare and SQL Server. It is a constantly changing equation that requires review and adjustments. ABCloudz experience can help you with finding your center with virtualization and databases.

About the Author:

Thomas Roehr is a Senior Solutions Consultant for ABCloudz. He has been involved with Information Technology since the late 1970’s, and working with Unix, C, and SQL from 1985 onwards.