[Note from Pinal]: This is a new episode of Notes from the Field series. A common comment I often hear from the developers is – “I have virtual environment and I have followed all the tricks and tips which I should apply to SQL Server but still I do not see a performance improvement to my server. I believe virtualization is not a good thing.” The matter of the fact is that we do not know how to tune virtualized server. Regular SQL Server where we have dedicated server and virtualized SQL Server have few different tricks associated with them. Let us learn about Virtualized SQL Server Performance and Storage System.
In this episode of the Notes from the Field series database expert David Klee explains a very crucial issue DBAs, and Developer faces in their career – how you can improve performance of overall server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of David in his own words.
Out in the field, the sheer number of trouble scenarios that you run into is mind boggling. But, over time, you start to see a number of patterns appear, and you start to build this list of cause-and-effect relationships in your mind. The list of common patterns helps you be more efficient at troubleshooting different problems. In having virtualized SQL Server and other mission-critical workloads for quite some time now, storage performance is the number one recurring item on that list. Let’s discuss why this is, and how to get underneath the symptoms and into the root causes of some of these common themes.
Performance Collection
Paramount to any discussion on storage performance, in both the physical and virtual worlds, is performance statistic collection. What amount of I/O performance do your SQL Servers consume throughout the day or week? You cannot begin a conversation about good or bad storage performance without having a usage baseline to compare the current system state against. Use your favorite performance statistic collector (Perfmon is mine) and collect data around the clock. Use this data to start to trend your usage. Common Windows performance counters that you should sample for disk include:
| Counter Group | Counter Set |
| PhysicalDisk | % Idle Time |
| Average Disk Bytes / Read | |
| Average Disk Bytes / Write | |
| Average Disk Read Queue Length | |
| Average Disk sec / Read | |
| Average Disk sec / Write | |
| Average Disk Transfer Bytes / sec | |
| Average Disk Write Queue Length | |
| Current Disk Queue Length | |
| Disk Read Bytes / sec | |
| Disk Reads / sec | |
| Disk Write Bytes / sec | |
| Disk Writes / sec |
Remember to use the option to sample all of the drives individually instead of just the total aggregate counter.
Scenario
Take this scenario as an example. You currently have 20 mission-critical SQL Servers that you are looking to virtualize. Each one exhibits steady-state business-day storage utilization of 8,000 IOPs (with bursts to 15,000) and 45MB/s (and bursts to 275MB/s). Given the state of your virtualization environment regarding CPU and memory consumption, you feel comfortable with six SQL Server virtual machines per physical host. Do the math and do you find that each host can handle the aggregate storage workload? It equals between 48-90K IOPs and between 270-1650MB/s, per host.
Uh-oh.
Virtualize these machines and you most likely cannot achieve that level of performance at each host. You now have a performance bottleneck based on your physical server’s performance, and you are going to notice.
Now What?
Each of your hosts and shared storage devices (usually a SAN) should be carefully examined before you virtualize your SQL Servers. The SAN itself should be configured in a manner that can exceed your aggregate I/O requirements, both per host and per LUN. RAID types, controller counts and cache values, disk group configurations, whatever – should all be able to exceed what you need. Most of the time, the core SAN can handle your requests without a problem. Other times, more spindles or SSDs, controller cache, or reconfiguring RAID types can possibly fix the situation.
At the host level, though, things get interesting. Each host will have some means to connect to the storage (fiber HBAs, Ethernet adapters, etc.). A default configuration is usually what I see in the field, which is usually not optimal for resource-intensive workloads such as SQL Server. Round-trip path performance may be slow, active multipathing may be misconfigured or not configured at all, individual paths can be congested and not load balanced, or not enough overall paths to the SAN may be available. Any one of those items can cause an individual VM’s performance to suffer, and in aggregate this impact can be very devastating to the overall performance of all of the VMs on a host.
At the VM level, you have some tricks that can help you improve performance even more. Configure your SQL Server VMs with multiple virtual disks and spread out your workload. A common disk layout for my SQL Server templates is as follows.
| Letter | Purpose |
| C: | Operating system |
| D: | SQL Server home and system databases |
| F: | User database data files (1 of X) |
| L: | User database log files (1 of X) |
| T: | TempDB database data and log files |
| Z: | SQL Server database backup target |
By utilizing multiple virtual disks, you allow the hypervisor to more efficiently multipath the I/O requests rather than funnel all of that I/O traffic down one route. You can then place the virtual disks on the appropriate type and configuration of storage. For example, the OS disk can go on a SAS tier of disks. The backup volume could go on a SATA tier, as well as rarely used archival data. Commonly used data could get placed on SSDs. Maximize your performance where you need it, and minimize costs where you don’t quite need top performance.
Another quick tip is that for VMware-specific environments, you can utilize the VMware Paravirtual SCSI driver. You can get a sizable performance improvement for a virtual disk. Michael Webster has a great post demonstrating the performance differences here, and I have a how-to guide that you can use to retrofit your existing I/O-intensive virtual machines to take advantage of this free performance boost here.
Conclusion
Hopefully your virtualized SQL Servers and other mission-critical systems are performing beautifully in your virtual environment. If not, review these tips to see what improvements you can make. Even if things are good right now, incorporate these tips to help squeeze more performance from your virtualized SQL Servers to help you scale into the future!
If you want to get started with performance analytics and triage of virtualized SQL Servers with the help of experts, read more over at Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
