Monitoring Tintri VM Stores

This post has been updated to include a link to our Strategies and Best Practices for Virtualizing SQL Server guide.

The widespread deployment of production SQL Servers in virtualized environments is moving forward at a rapid rate. Monitoring, troubleshooting, and performance tuning storage systems supporting SQL Server database files in these environments remains one of the more complex and vexing issues facing many DBAs.

Recent releases of Performance Advisor have focused on providing data professionals with the insights needed to view, monitor, troubleshoot and implement intelligent alerting in these environments. If you haven't been keeping up, these blog posts will help you get your learn on.

Most recently, we have partnered with vm-optimized storage vendor Tintri to integrate performance metrics and visualizations from their VM Stores into SQL Sentry. Because Tintri storage is designed specifically for virtualized and cloud environments, this partnership and integration is a natural fit for us, Tintri, and, most importantly, our customers. I invite you to spend some time on Tintri's product page to learn more about their VM-aware storage options.

How Does It Work?

As   mentioned, Performance Advisor auto-detects any Tintri VM-aware storage (VAS), labels it as such on the Disk Activity and Disk Space tabs, AND adjusts our performance thresholds to better reflect expectations from a Tintri device.

What Does It Look Like?

The screen shots below show graphs from the Disk Activity and Disk Space tabs on a monitored virtual host with virtual disks hosted on a Tintri VAS.

Host Disk ActivityV
Virtual Datastore Disk Activity

Host Disk SpaceVirtual Datastore Disk Space

Each rectangular icon represents a virtual disk, regardless of which virtual host the disk resides on. Icons of the same color reside on the same VM. As you can see, the call-out displays the name of the VM and the file, along with current performance metrics. As always, all this information is also displayed in the Disk Activity and Disk Space tab grids.

New Charts!

On monitored VMs, the Disk Activity and Disk Space tabs include three new charts displaying context-appropriate information for Latency (in milliseconds), Normalized IOPS, and Normalized Throughput (in MB/sec).

Host Charts
Tintri Performance Charts

The latency chart displays reported latency values from the Host, Network, and Storage layers. This chart uses the same colors for these metrics as SAN administrators see in Tintri's control panel. If Tintri's Quality of Service (QoS) feature is applied, Throttle latency also appears. A unique feature of this chart is the inclusion of Windows Host read and write latency overlays.

Host Latency Overlay
Storage Latency Chart with Windows host latency overlay

The bottom series in the IOPS and Throughput stacked charts (displayed in solid colors) are VMs on the current host. The grayed-out series reside on other hosts. Hovering over any segment of the stacked charts displays additional detail information such as Host Name, VM Name, Time and metric value. This provides DBAs with a clear visualization of which VMs are having the most significant impact on the VM store.

Host IOPS Detail
Normalized IOPS Chart with guest vm detail

Selecting one of the virtual disk icons on the graph, or in the grid view, displays the same 3 charts with metrics from the selected virtual disk:

Disk Charts
Virtual Disk Latency Charts

Of course you can also see the same charts in the context of a VM guest:

Guest Charts
Virtual Machine Latency Charts

But wait! There's more!

All of this is great information, much of it not available elsewhere, and yet there's additional big value here for DBAs (and Devs) by correlating these performance metrics to your workload. Of course, you already know how to do that.

Using our example, I select an area surrounding one of the IOPS spikes, right-click, and choose Jump to > Top SQL.

Here's a subset of the result set:


Having identified the highest impact stored procedure, I click on the View button which opens its estimated query plan in the SQL Sentry client. In one or two more clicks I can find the operator with the highest logical reads and I'm on my way to tuning this query.

Query PlanQuery Plan with High Read Clustered Index Seek Highlighted

Thwack - Symbolize TM, R, and C