SQL Server and VMware

SQL Server on VMware Best Practices – How to Optimize the Architecture

A couple of months ago, VMware released a new white paper called “Architecting Microsoft SQL Server on VMware vSphere”. Published in August 2018, it spells out the best practices for running SQL Server on vSphere. Twenty years ago VMware launched and forever changed the compute world.

SQL Server and VMware go together like peanut butter and jelly. Read on to look into the details…

First – you should read the white paper then come back…I’ll wait…

Architecting Microsoft SQL Server on VMware vSphere

I will point out some key points that are practical to implement. The whole paper is worth a read; however, I will explore only a few parts. We won’t talk about extra VMware features such as vMotion. The document contains many citations you can research to go more in depth should you choose.

Right-Sizing Your VM

overflowing
Image / License – Do not overflow your servers!

Understanding your SQL Server workloads will help determine the level of each resource required. Our Service Level Agreements (SLA) will dictate which hardware we run on. OLTP vs OLAP databases can differ in approach. All of this is useful when planning to virtualize SQL Server on VMware.

David Klee has some well written posts that explain the concept of right-sizing your VM. By this we do not mean capacity planning – that is a different subject. Due to the nature of the hypervisor and virtualization uses there are key differences with regard to resource allocation compared with a physical server.

Right-sizing is picking the appropriate level of resources for each guest VM. We want it to handle sustained peaks of utilization without impacting other guests on the host. This is incumbent upon base-lining and measuring performance over a period of time.

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.

ESXi Host Configuration

I wrote about best practices in my SQL Server Environmental Diagnostics Guide – VMware. In it are a lot of reminders about which configurations should be changed from default and which left alone. Let’s break down the following topics:

  1. Power Settings
  2. CPU Configurations
  3. Memory Options
  4. Storage Considerations
  5. Virtual Networking

Power Settings

The settings you have for power supply can greatly affect system performance. Quoting from the white paper:

As a best practice, update the BIOS/UEFI firmware on the physical server that is running critical systems to the latest version and make sure all the I/O devices have the latest supported firmware version
In vSphere versions 5+ the default power setting is balanced. For a VM running SQL Server this is inadequate. Change it to high performance. This setting has a bigger impact on performance that you may think.
VMware power setting
Figure 7 – Recommended ESXi Host Power Management Setting

CPU Configurations

Pay attention the the virtual sockets and virtual cores you specify for the guest VM.

Be careful not to exceed the number of physical cores on the ESXi host to any guest!

Some other tips and reminders:

  • Enable hyper-threading
  • Create less sockets than cores for each VM – not the other way around!
  • Do not use CPU affinity because it limits the hypervisor from efficiently scheduling vCPUs on the physical server

Here is a table from the document showing standard sized VM examples:

Standard sized vm configuration examples
Table 2 – Standard VM Configuration: Recommended vCPU settings for Different Number of vCPU

Memory Options

The big thing to watch out for with regard to memory allocation is memory ballooning. Basically this is when the hypervisor has to pull memory away from another VM to give it to another who needs it. This will bog down performance! Even a little (any positive value) will be very noticeable!

Remember not to over-commit memory to any guest VM!

You have the option to set a memory reservation. Here is depends on what guests are running on the ESXi host. I tend to not set a memory reservation but there are times when it can be a good thing.

memory reservation
Figure 25 – Setting Memory Reservation

Just exercise caution here and setting a memory reservation can affect the performance of other VMs. Section 3.7.3 explains the balloon driver well.

Storage Considerations

The most common method is to store the virtual machine file system on a shared storage subsystem.

VMware storage stack
Figure 28 – VMware Storage Virtualization Stack

Make sure to use the paravirtual SCSI adapters as your disk controller. For the VM executing SQL Server, be sure to split up your disks for your database files, transaction logs, and tempdb. This will reduce I/O contention.

Virtual Networking

Virtual Networking best practices are in section 3.9.2. It spells out the guidelines for provisioning network to a SQL Server VM. Enable jumbo frames for vSphere – especially when performing a migration using vMotion.

Guest VM Configuration

We talked about a few applicable things above that apply to the guest VM level. For example: always set the power option to high performance and  use a PVSCSI adapter for your disk controller driver.

Many organizations have policies that state every server must have AV scans – even SQL Server. This can be argued if it is a wise choice or wasted operation. However, if you must run anti virus scans on your SQL Server then please white-list the database files and binaries.

Memory allocation to SQL Server is similar to physical. Typically we set the minimum memory allocated to SQL Server to zero and the max at an appropriate value. For tier 1 mission critical systems, VMware suggests setting both min and max memory to the same value. Additionally they recommend using LPIM (locked pages in memory).

For CTFP and MAXDOP the advice is the same as with a physical server. You can read about all the best practices for configuring SQL Server: SQL Server Environmental Diagnostics Guide.

High Availability – SQL Server on VMware

You can utilize any of the HA features available for SQL Server while virtualized.

Here is a table summarizing the choices:

high availability options
Table 1 – SQL Server 2012+ High Availability Options

SQL Server has a relatively large install base among RDBMS products. For the past 20 years organizations have been increasingly been using virtualization to reduce cost and more efficiently use their compute resources. VMware is the leader in the field – that is by far the most common hypervisor out in the wild. Therefore, a good DBA needs to know a little about virtualization. Otherwise you are missing out.

Thanks for reading!

 


If you liked this post then you might also like: SQL Server Environmental Diagnostics Guide – VMware

Did you find this helpful? Please subscribe!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

7 thoughts on “SQL Server on VMware Best Practices – How to Optimize the Architecture

  1. FYI, for those getting into the SQL configuration (listed under Guest VM Configuration), I suggest you look at dbatools.

    dbatools is sort of like a command-line SSMS. The project initially started out as Start-SqlMigration.ps1, but has now grown into a collection of over 400+ commands that help automate SQL Server tasks and encourage best practices.

    Commands to do you suggest:
    – Test-DbaMaxDop/Set-DbaMaxDop
    – Test-DbaMaxMemory/Set-DbaMaxMemory
    – Get-DbaPrivilege/Set-DbaPrivilege
    – Get-DbaSpConfigure/Set-DbaSpConfigure

  2. I really wish documentation about configuring SQL would stop the “typically DBA’s ask for more CPU and Memory than necessary.” These days with VM’s we know that this is easier to change these allocations than in the past and documentation written with these words put all DBA’s in a certain bad light. You perpetuate this thinking in our Server admins minds and in doing so you do DBA’s harm. Please update your documentation to not bad mouth DBA’s. We are professional and understand this technology too. Thank you

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.