If you run Microsoft SQL Server on VMware vSphere, you do so to have more flexibility, high availability, and a gain in performance. But if you do it wrong, you might end up with the exact opposite: bad performance and frustration. In this post, we'll have a look at some basic configuration, then at the ESXi and hardware (BIOS) level, and eventually at the VM level.

Before we start, make sure to read the current best practices on SQL Server and VMware. Prior to jumping in to start deploying SQL Server on VMware vSphere, let's take a look at the business requirements first.

The business requirements always dictate the overall architecture because you might have business-critical DBs requiring lower recovery point objectives (RPOs) and recovery time objectives (RTOs) than other DBs not as critical. Thus, separating these different kinds of DBs on different VMs might be necessary.

This affects resource allocation. You'll allocate more CPU, memory, disk, and network I/O to resource pools containing these highly performant VMs rather than DBs that are smaller, usually used without a real-time intensive workload crucial for a company's business.

VMware vSphere's built-in protection against hardware failure ^

One of the main reasons why we virtualize is because of better protection against hardware failures. VMware vSphere offers basically two different protections that prevent downtimes.

You can also choose to protect the applications against failure with vSphere High Availability (HA) or vSphere Fault Tolerance (FT). Whereas HA can restart a VM on another host after a host failure, FT can simply keep the VM with the application running with no downtime.

FT maintains a secondary copy that runs as a "shadow VM" on another host. This VM runs a few milliseconds behind the first VM. Actually, it's a mirror VM. Thus, if the first VM is lost because of hardware failure, the shadow VM become the first VM, and FT automatically clones a new shadow VM to another host in the cluster.

Note: This scenario needs at least three hosts within a vSphere cluster.

You can combine vSphere protection features with native SQL Server Always-On Availability Groups, Always-On Failover Cluster Instances, database mirroring, and log shipping.

ESXi optimization tips and virtual machine sizing ^

We should optimize each ESXi host from the ground up. This means you must start at the BIOS level by enabling Turbo Boost, Hyper-Threading, and non-uniform memory access (NUMA) in ESXi hosts in the BIOS/UEFI. NUMA is a computer memory design used in multiprocessing. Also, you must enable VT‑x/AMD‑V, Extended Page Tables (EPTs), and Rapid Virtualization Indexing (RVI). Also at the BIOS level, disable any unused devices (such as serial ports). You should set power management in the BIOS to OS controlled, which enables the ESXi host to control power management based on policy.

Adjust this policy accordingly for SQL workloads.

Set the power management policy

Set the power management policy

There is no reason for giving a larger number of vCPUs to a VM hosting a SQL Server than it requires at the time. In fact, by giving that VM the exact number of vCPUs, we'll keep the overhead low and still satisfy the required resources to optimize the performance.

When conditions change, you'll have greater demand and can easily resize the VM. You can add on more vCPUs with or without a reboot. The number of vCPUs should fit on one NUMA node.

If you have large SQL Servers currently with many databases, you should consider dividing them into smaller VMs where each of them fits on a NUMA node rather than spanning two NUMA nodes at once. This will improve performance.

If not possible for larger workloads and large databases, you should span NUMA nodes but as sparingly as possible, and always try to do so as a last resort.

For memory consumption, you should try to confirm memory consumption rates using SQL Server-level memory metrics rather than rely on Windows-monitoring utilities before adjusting the memory for that VM. It's important not to over-allocate memory because this leads to VM memory overhead and perhaps even to memory contention.

You can use SQL Server built-in tools such as Dynamic Management Objects (DMOs), performance counter values, SQL Server Data Collector, and so on if you have just a couple of SQL Servers. When you have a larger number of SQL Servers within your environment, it's probably a good idea to find a script to gather all of these metrics from different SQL Server workloads for you.

You should make a memory reservation for your SQL Server memory resources. Memory reservation will guarantee the that no bottleneck from the memory side will occur because the percentage of memory reserved will always be available to the VM even if there is resource contention that might affect other VMs without VM memory reservation set.

To optimize the VM even further, I'd recommend removing any unused virtual devices and make sure to use the paravirtual storage adapter for all disks and make sure all NICs use VMXNET3 optimal network adapters.

vSphere storage considerations for SQL Server workloads ^

One of the main reasons for bad performance is and always was storage. As SQL Server workloads consume a huge number of input/output operations per second (IOPS), it's crucial to select your datastore accordingly.

VMware recommends placing SQL Server workloads on Virtual Machine File System 6 (VMFS6) formatted datastores. (vSphere 6.5 and 6.7). You should definitely avoid placing a VM hosting SQL Server on a VMFS3 or VMFS3-upgraded datastore. It will negatively affect disk performance and thus the overall VM performance.

For the most critical databases where performance requirements are the top priority, you should have a one-to-one mapping between Virtual Machine Disks (VMDKs) and logical unit numbers (LUNs). This means one VMDK per datastore.

Note that supported storage types include Network File System (NFS) and raw device mapping (RDM) in physical compatibility mode. vSphere version 6.0 and later support vMotion of a VM with an RDM disk in physical compatibility mode.

SSD backed-up datastores are necessary for high-performance SQL Server workloads, so consider using all-flash storage.

Final thoughts ^

We've just scratched the surface for running Microsoft SQL Server on VMware vSphere. It's way more complex in reality, and a single post is not sufficient to cover all possible optimizations and best practices. We've just given an orientation and a direction. Hopefully the tips above will help you get started with optimizing your virtualized SQL Server installations.

Subscribe to 4sysops newsletter!

For further information, I highly recommend downloading a free PDF from VMware called Architecting Microsoft SQL Server On VMware vSphere, which they regularly update.

1 Comment
  1. Varun 3 years ago

    Amazing. This has made a real change in the performance of our SQL servers in the business. Thanks for the post.

Leave a reply

Please enclose code in pre tags

Your email address will not be published. Required fields are marked *


© 4sysops 2006 - 2021


Please ask IT administration questions in the forums. Any other messages are welcome.


Log in with your credentials


Forgot your details?

Create Account