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.
As Jason Hall noted in his blog post introducing SQL Sentry v10, 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.
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.
Virtual Datastore Disk Activity
Virtual 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.
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).
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.
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.
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:
Virtual Disk Latency Charts
Of course you can also see the same charts in the context of a VM guest:
Virtual Machine Latency Charts
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. Hint… Jump to!
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:
Top SQL
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 Plan with High Read Clustered Index Seek Highlighted
Just a reminder. What you are seeing here is pre-release, but you can expect to see these enhanced features in a Performance Advisor release very soon!
<![endif]–>
