Success Starts with a Great Plan

by Kyle Wassink on June 26, 2018

The information you glean from any database management system is only as good as the query created to retrieve the necessary data. That’s why it’s so important to understand and optimize the query plans written to find and get to that data. And while SQL Server query plans can provide a wealth of information you can use to diagnose query performance problems, those plans can be confusing and difficult to understand.

That’s why earlier this year we were excited to announce new enhancements to the vRealize Operations Management Pack For Microsoft SQL Server that provide, among other things, enhanced query plan analysis support. So let’s dig a bit deeper into the Query Plan capability and discuss what query planning is and how this new functionality works in vROps.

Why query planning is important
Query execution planning takes a given query and breaks it down into the underlying steps the database will perform to provide the output the query wants.

Blog - Success starts with a great plan

By breaking a query down to the individual steps the database will perform, inefficient query statements or missing table indexes can be identified.

How query planning works in vROps
With the latest Blue Medora Microsoft SQL Server management pack installed and configured, head to the “Microsoft SQL Server Query Plan” dashboard, found in the “All Dashboards” dropdown.

Blog - Success starts with a great plan
Figure 1: Open the “Microsoft SQL Server Query Plan” dashboard.

Using the upper left list widget, find and click the SQL Server Instance you want to investigate. All your configured SQL Servers should appear in the list. If you have a large number of instances to dig through, you can use the filter to expedite your search.

Blog - Success starts with a great plan
Figure 2: Use the filter if necessary to find the SQL Instance you want to investigate.

Once you select an SQL Instance, the upper right widget will automatically populate with the queries featuring the highest average execution time. Click on any one of these queries to get more information aboutit.

Blog - Success starts with a great plan
Figure 3: After selecting a SQL instance (top left) and query (top right), the bottom two widgets populate.

The bottom left widget in Figure 3 will be used to start the query plan analysis, while the bottom right widget displays the actual query text to be analyzed. The vROps action framework is used to process the query analysis on-demand, so we need to kick off the analysis job using the “Selected SQL Query (Use action to generate plan)” widget.

Blog - Success starts with a great plan
Figure 4: Hit the gear icon in the upper left of the widget, and then click “Retrieve Query Plan”.

In the popup, validate the query text then click “Begin Action.” You’ll then get a Task ID link to see the task progress. Figure 5 below shows what that page looks like.

Blog - Success starts with a great plan
Figure 5: Status is completed, so the query plan is ready. Head back to the Dashboard and reselect the SQL Instance and Query to see the output.

Once the query planning task has completed, refresh the dashboard and scroll down to the bottom “Query Plan” widget. You’ll get a breakdown of the different steps the query will take to retrieve, filter, sort, etc. the data the query asks for and the relative cost (with a 100% total cost) of each operation.

Blog - Success starts with a great plan
Figure 6: The Query Plan output for the selected query.

Finally, hovering over any of the sections will provide more insight into whether it is CPU intensive, I/O intensive, how many rows it estimates it will have to process (for ROW execution mode) and much more.

Blog - Success starts with a great plan
Figure 7: A deeper look at the most “expensive” portion of the query, taking up 61% of the total cost.

Get everyone on the same page
VMware’s vRealize Operations with Blue Medora packs can be leveraged IT-wide, from virtualization admins to DBA’s and more. Query planning in vROps is a major benefit for database administrators looking to better understand their databases and the underlying virtualization and infrastructure supporting them. To learn more about the Microsoft SQL Server pack and other vROps integrations, visit BlueMedora.com.

Get started

Try BindPlane for free. No credit card required.

Sign up
True Visibility
BindPlane for VMware vRealize Operations

True Visibility allows cloud management teams to use VMware vRealize’s powerful machine learning and capacity planning engine across their entire hybrid cloud environment.

Azure Monitor...everything
BindPlane for Microsoft Azure Monitor

Make Azure Monitor your first-pane-of-glass across your entire multi-cloud, multi-database or hybrid platform environment.

Thank you for contacting us. Your information was received. We'll be in touch shortly.