SQL SERVER – Virtualized SQL Server Performance and Storage System – Notes from the Field #013

[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.

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 (http://blog.sqlauthority.com)

About these ads

One thought on “SQL SERVER – Virtualized SQL Server Performance and Storage System – Notes from the Field #013

  1. Pingback: SQL SERVER – Contest Post – Notes from the Field – Learning Performance Tuning and Database Health | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s