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.
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).
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.
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.
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:
Of course you can also see the same charts in the context of a VM guest:
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:
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.
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!