Optimizing SQL Server Performance in a Virtual Environment

Just a few years ago installing SQL Server on a virtual machine (VM) was pretty much taboo. Today, this is no longer the case. Installing SQL Server on a VM is a common practice in many SQL Server deployments. Here are seven tips for achieving optimal performance when running SQL Server on a VM.

1. Check CPU Usage Regularly

One of the most common problems with running SQL Server on a VM is oversubscribing the physical CPU cores. In other words, there are too many VMs sharing the same physical CPU cores. This leads to task switching at the host level, which will be seen as latency in the client OS.

Related: 5 SQL Server Virtualization Tips

In VMware vSphere (formerly VMware ESX), you can run the esxtop command to check CPU usage. Look for the CPU Load Average numbers, which appear in the top row. These numbers represent the average CPU load in the last 5, 10, and 15 minutes, respectively. A load average of 1 means that your host’s CPUs are fully utilized. Any number over 1 indicates that the host needs more CPUs to handle the workload.

You should also look at the %USED numbers. They represent the percentage of CPU workload used by the cores. If any of these numbers start going over 75 percent on a regular basis, the physical CPU cores are oversubscribed and the server could use some more cores. If you have VMware vCenter Server (formerly VMware VirtualCenter), you can use the data it captures to get a historical look at CPU usage.

In a Microsoft Hyper-V platform, you look for the same kinds of information. Although Hyper-V doesn’t have a command like esxtop, you can use Windows Performance Monitor to see the CPU load percentages for the physical server. The specific counters to look at are Processor: % Processor Time and Processor: % User Time, as shown in Figure 1.

If you have Microsoft System Center Virtual Machine Manager (VMM), you can use it for historical trending. If you don’t have VMM, you can export Performance Monitor’s data to SQL Server or another application for historical trending.

2. Disable Memory Balloon Drivers

Both vSphere and Hyper-V use memory balloon drivers, which are software drivers installed on the guest computers. They allow the host OS to reclaim physical memory from a guest OS so that the memory can be used by another VM or the host OS. When the host OS begins to see memory pressure, it uses the balloon driver to require that a guest OS flush some data from memory so that the memory can be reallocated. This is done automatically and without any interruption to the guest OS. At first glance, this might seem like a bad thing to have happen, but it can be good or bad, depending on how much memory the guest computer has, how much memory the host has, how much memory the balloon driver is trying to reclaim, and many other factors.

Some people advocate disabling the memory balloon driver on guest machines running SQL Server. However, if you disable it, the host machine might become starved for memory because the host OS wouldn’t be able to reclaim memory from the guest OSs, thereby causing the host OS to begin swapping large amounts of memory from the physical RAM to the host’s swap file. This scenario might occur, for example, on the startup of new VMs or if VMs are being migrated from one host to another.

Although VMware has used balloon drivers for a while, Microsoft recently added them to Hyper-V. For the balloon drivers to work in a Hyper-V environment, the host OS must be running Windows Server 2008 R2 SP1 or later.

3. Don’t Assume All Database Server Memory Can Be Shared

One of the big selling points of the VMware platform has been its ability to share running memory to allow for overcommitment. In Hyper-V, this capability (called dynamic memory sizing) was introduced in Server 2008 R2 SP1.

For virtual Windows web servers and application servers, the percentage of memory that can be shared ranges from 50 percent to 90 percent, depending on what is running and what is loaded into memory. For virtual database servers, this number will be much lower—typically in the 2 percent to 3 percent range if they’re running with large amounts of RAM (64GB or more) allocated to each VM. The reason for the much lower percentage is that most of this memory won’t be able to be shared. To be able to share memory, the virtual servers would need to have the same database and need to have the same pages loaded into memory. Given all the globally unique identifiers (GUIDS) and hashes stored within the procedure cache and buffer cache, it’s safe to assume that the memory used by them would never be able to be shared.

Even with as little help as memory sharing provides, it’s still useful, so you shouldn’t disable it. It’s just important to understand how memory sharing works so that proper memory oversubscription assumptions can be made. Assuming that all database server memory can be shared will lead you to a poorly designed virtualization solution.

4. Get Back to the Storage Configuration Basics

Storage is the one place where you really need to get back to the basics, because storage for a busy virtual SQL Server machine should be configured just like it would be in the physical world. Here are the most important basics to remember:

Note that VMs’ OS disks are typically configured on RAID 5 or RAID 6 arrays because most OS disks shouldn’t have a lot of write I/O being generated after the OS is installed. OSs typically generate only read I/O, as binaries are read from the disk when applications are loaded into memory.

5. Deduplicate Data

If your storage array supports online or live data deduplication, using it can help improve overall environment performance. In addition, deduplicating OS disks can save a lot of space because so much of the Windows OS is the same from server to server. Because the virtual disks point back to the same physical block, the array only needs to load the block once into memory, which leaves a lot of extra space for other more important data in the array’s cache.

For example, Server 2008 is around 20GB to 30GB, with about 2GB typically loaded into memory. So, if you have 50 VMs, the array needs to load 100GB of data when they boot up. If you use data deduplication, the array only needs to load 2GB to 3GB of data. The rest of the VMs can then read those blocks from the cache. Although this will take some extra CPU power to implement, the overall benefit is worth the occasional CPU hit on the array.

6. Disable the Lock Pages in Memory Option

There’s a lot of debate about whether the Lock Pages in Memory option should be used in SQL Server at all. Most of the time, it shouldn’t be. This is just as true in a VM as it is in a physical server. In fact, it can cause even more problems in VMs because it interferes with the memory balloon driver. As I mentioned previously, the memory balloon driver can reclaim memory from a guest OS when the memory is needed elsewhere. If the Lock Pages in Memory option is enabled, the guest OS can’t return that memory to the host OS, which would cause the host OS to become starved for physical memory.

7. Monitor the VMs and the Physical Host

Monitoring is key to ensuring that you can maintain a high-performing virtual environment. You need to monitor the performance data generated by the VMs (just like you would physical machines) and the physical host, comparing the two sets of data. By drilling down from the host data to the guest data (or vice versa), you can more easily identify problems and see how they’re affecting both the physical and virtual environments.

Monitoring a single Hyper-V server is pretty easy to do—and it can be done for free if you use Performance Monitor. You can capture the physical host’s CPU (Processor: % Processor Time counter), memory usage (Memory: Available Mbytes counter), and I/O information (Physical Disk: Avg. Disk sec/Read and Physical Disk: Avg. Disk sec/Write counters).

As your Hyper-V deployment becomes larger, you’ll probably want to purchase tools for server-level monitoring. Microsoft offers System Center Operations Manager and VMM. With Operations Manager, you can monitor production Hyper-V clusters. You can also use it to monitor the VMs, letting you easily compare the two sets of data. VMM is a tool for managing production Hyper-V clusters, but you can also use it to monitor the performance of those clusters.

You can monitor a single vSphere server using the free VMware vClient tool. The big downside to this tool is that it doesn’t track historical data when connected to a single virtual host. However, vCenter Server tracks historical performance data for hosts and guests. It tracks dozens of different metrics every few seconds and rolls those numbers up to hourly, daily, weekly, monthly, and annual averages for long-term data trending.

If you’re using both Hyper-V and vSphere, you’ll probably want a single monitoring and management interface. If you have vCenter Server 3.5 or later (which is required for clustered vSphere servers) and VMM, you can monitor and manage both the VMware and Hyper-V platforms with VMM. That way, you’re monitoring and managing the entire virtual environment from a single application.

Third-party tools are also available for monitoring virtual environments but they typically work on only one type of host platform. For example, Confio Software’s IgniteVM supports monitoring the host and guests in a VMware environment.

Things to Keep in Mind

Whether you’re in the process of building a SQL Server virtual environment or you just want to improve the performance of an existing one, keep these seven tips in mind. The most important thing to remember, though, is that not every SQL Server machine should be virtualized. Some SQL Server machines need too many resources to move into a virtual platform.