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.
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.
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.
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.
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.
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.
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.
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.
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.