SQL Sentry Performance Advisor: Disk Activity Tab

SQL Sentry Performance Advisor provides a substantial amount of information about your SQL Server environment. I'd like to focus today on our patented Disk Activity view. In many SQL Server environments, disk I/O is one of the biggest bottlenecks. Being able to see which drives and database files are most active can help you to see what might be causing those bottlenecks. Additionally, as you'll see, we can also identify latency on those drives.

There are two portions to the Disk Activity view - a graphical view and a grid view. I'll be showing the graphical view, but the grid view shows the same information in a numerical grid format.

Going from left to right in the image above, you see the underlying storage system, the controller(s), physical drives, logical drives and mount points, and then your database files. The pipes on the bottom of each drive represent write activity, while the pipes at the top show read activity. The database files for each database are all the same color - the data files are plain, and the log files have diagonal stripes.

Hovering over any of the files will give you further information for that file, including the name, path, size, and performance counter information:

.

Using the Disk Activity toolbar, you can toggle between looking at the sum of reads/writes, or the average for that time period:

Like all of Performance Advisor, you can look at Disk Activity either in live mode or for a particular point in time. The image above is showing disk activity for a specific point in time. When viewing it in live mode, the read/write lines will be dashed and will simulate actual data movement:

.

For the most part, I use the Disk Activity view to help me troubleshoot issues during previous points in time. One of the most important insights is the way SQL Sentry can display latency. You'll notice in the image above that the pipes displaying read and write activity are various colors and thickness. This is so that you can quickly see which files and/or drives are experiencing latency. Here are what those colors indicate:

Knowing this, you can quickly identify which drives and/or files are currently experiencing latency. If you are seeing continuous issues for a file or a drive, you can right click on any file, which provides the option to run a Quick Report on disk activity. This report provides quantitative information on the throughput, IOps, and latency for the files on that drive.

All of this provides a vast amount of usable information that would typically be hard to aggregate, but we're still moving towards making this even more useful.

If you haven't already, read Justin Randall's post on Monitoring Tintri VM Stores. This is a glimpse into how we're striving toward providing better information on virtualized storage solutions along with providing information for flash and SSD arrays.