SQL Sentry Plan Explorer Beta

Recently at SQLBits York I demonstrated the new SQL Sentry Plan Explorer (formerly known as the “Plan Viewer”) and officially announced its upcoming release. We are now pleased to make the beta version of the tool available for download!

Background

We had originally envisioned query analysis features only as part of the fully licensed version of SQL Sentry Performance Advisor. The goal was to build upon what we all have today with the graphical plan view in SSMS, and in a big way. Like many of you, I’ve been using this feature for years, going back to Enterprise Manager, and so had lots of ideas going in about what we could do to make plan analysis more efficient.

After a few significant reworks over many months, things really started coming together, and it was doing most everything I’d wanted and much more. Working with our lead developer, Brooke Philpott (blog | twitter), one idea had led to another idea, which led to another… it was a gradual, iterative process. I’m thankful that we embrace a style of agile development here, because this is one project that I believe would have been impossible to design to completion in advance.  It was only through using it on a daily basis to solve real world query issues that the tool was able to evolve into what it is today.

Anyway, somewhere along the way a couple of things became clear to us:

• These features are so cool and useful (IOHO) that we wanted to share the tool with anyone that wants it, so we are making it available to the SQL Server community for FREE!
• Much of the code was client-side, so it would be fairly easy to break out into a standalone tool without any dependencies on our collection service or database (one of each is required for every full SQL Sentry installation).

Plan Explorer Features

I’ll go ahead and run through some of the key features of the Plan Explorer, and in the process I’ll compare and contrast with SSMS. I am not trying to bash SSMS (promise! ;-), I’m simply trying to illustrate the reasoning behind the design of the tool for longtime users of SSMS/Enterprise Manager.

When you launch the tool for the first time, you’ll notice is that the UI is broken into 3 major sections:

• Statements Tree
• General Query & Plan Info
• Plan Details

They’re all empty until you load a plan.  There are several ways to load a plan:

• In SSMS, right-click a graphical plan and select “Show Execution Plan XML”, then copy and paste the plan XML into the Plan Explorer.  It doesn’t matter which pane is active, the clipboard handler is global and will auto-detect the plan XML.
• Save an execution plan from SSMS to a .sqlplan file, then open the file using the Open toolbar button, File->Open menu, or Ctrl + O. Raw plan XML files and .QueryAnalysis files (our own proprietary format) are also supported.
• Right-click an existing .sqlplan file in Windows Explorer and select “Open with -> SQL Sentry Plan Explorer.”
• Drag-and-drop a plan file onto the application.
• Retrieve the estimated plan from TSQL. (See General Query & Plan Info section below).

Statements Tree Section

Let’s start with how SSMS works.  A batch or stored procedure can of course consist of one or more statements. With stored procedure estimated plans in SSMS, all individual statement plans are joined together at the top level:

When it’s an actual plan for a batch or stored proc, or an estimated plan for a batch, each statement is broken out into a separate section, with embedded cost and other information in the section header:

When there is only one or a few statements, SSMS can do Ok with this approach. However, if it’s a large plan with tens or hundreds of statements and conditional logic, looping, etc., it just doesn’t scale. Some plans are so big and complex that it is literally impossible to find the heaviest statements and operations, and some can’t even be loaded by SSMS!

We wanted to change all that, and so the Statements Tree represents one of the biggest design differences between the Plan Explorer and SSMS. Its function is critical when navigating multi-statement plans. What it shows is a logical breakdown of the entire plan tree, including all control structures, nested procedure calls, and estimated and/or actual metrics for each statement, as well as operation counts by statement:

If you click on any statement row in the grid, you’ll be shown the associated plan for that statement only:

Likewise, if you click on any control structure (IF or WHILE) or EXEC <procname> higher up the tree, you’ll see a filtered view of the plan starting from that point:

As you’ve probably guessed by now, you can of course sort the Statements Tree by clicking on any column header, and voila!, the highest cost trees and statements will immediately bubble up to the top:

In the shot above I’ve sorted by Estimated Cost (all numeric columns sort DESC on first click). I can see that one of the subtrees has a nested DELETE that accounts for 27.2% of the total procedure plan cost, and I’ve have selected that statement row to view its plan. If you enlarge the image you’ll see that a clustered index seek (highlighted in red) accounts for 39.6% of the statement plan cost. So with a couple of clicks we’ve quickly ascertained the heaviest cost statement and operation in this rather large plan.

Whether there are 10 or 1,000 statements in the master plan doesn’t really matter, it’s trivial to find the most expensive areas so you can focus your attention on those. Big plans that simply weren’t usable before can now be managed with ease.

General Query & Plan Info Section

The primary tab you will make use of in this section is “Text Data,” which holds the color-coded TSQL batch or procedure definition:

If you loaded an existing execution plan, the TSQL will be auto-generated. You can also type TSQL, copy/paste it in, or open a .SQL file, then click the “Retrieve Estimated Plan” button on the toolbar to get the estimated plan.

NOTE: Executing TSQL to retrieve the actual plan is not yet supported, although opening an existing actual plan is fully supported.

You’ll notice that, like plans, the TSQL statements are synchronized with the Statements Tree, meaning that if you select a row on the tree view it will auto-select the associated TSQL statement and its execution plan, and vice versa.  See the shots above for examples.

Plan Details Section

This section contains 4 tabs, each of which serves a different purpose:

• Plan Diagram
• Plan Tree
• Top Operations
• Query Columns

If you select an operator node or row on one of the tabs, it will be auto-selected on all others, so you can switch back and forth between the different views of the plan without losing your place.

Plan Diagram

This is the view that will certainly be most familiar to everyone… although you should immediately notice some differences. First, there is color! That’s right, the cost information is color-scaled so you can quickly see which operations are heaviest, and Lookups and Scans are also highlighted (if Rows > 100):

Pretty cool, eh?  But wait, there’s more! 😉

If you right-click the diagram, context menus provide access to a variety of other functions. (bolded because this can be easy to miss!)

Using the context menus, in addition to scaling by total cost (CPU + I/O), you can also use CPU or I/O separately:

This can be very helpful if your hardware is more I/O constrained than CPU constrained, or vice versa.

To see which subtrees are most expensive, select the “Cumulative Costs” context item:

I won’t go into detail on all of the other plan diagram enhancements in this already lengthy post, but here is a list of some of them:

• Optimized plan node labels prevent truncation of object names in most cases, with menu option to disable truncation completely so full object names are always visible.
• Optimized tooltips prevent wrapping of object names, column names and predicates.
• Costs are always displayed above nodes for maximum readability.
• Costs are shown to the first decimal place.
• Connector line width can be scaled by either Rows or Data Size.
• Rows and Data Size labels are displayed above connector lines.
• Connector lines for bookmark (key|rid) lookups, table spools, and function calls show the true estimated rows or data size, and line width is scaled accordingly. (SSMS always shows the “per execution” rows, CPU and I/O costs for these operators in estimated plans, which can appear to dramatically understate the true costs. For example, if estimated rows=10 and estimated executions=1000 for a key lookup, the line is scaled to 10 rows, not 10,000! What makes this more confusing is that for actual plans SSMS does show the true rows and costs. So you really have to pay attention to the type of plan you’re looking at in SSMS – if you’re conditioned to always look for the operators with the fattest lines, you can completely miss the highest cost operations.)
• The mouse scroll wheel can be used for both scrolling up down, and zooming in/out (by holding Ctrl)!

Last but certainly not least, you may notice that some plans seem, well, “different.” This is likely due to the optimized layout algorithm which is able to render many plans using much less real estate than SSMS, meaning you are seeing more of the plan without having to scroll and zoom. It’s easiest to demonstrate this with a couple of pictures. The shots below are of the same plan:

SSMS version:

Plan Explorer version:

Plan Tree

This view is just what it sounds like, a tree representation of the plan which shows all operations and associated metrics. On the surface it looks similar to a showplan_all, however it’s different in several respects:

• You can expand and collapse sections, and sort within levels.
• Additional metrics are shown, such as “Estimated Data Size,” as well as “Actual Rows” and “Actual Data Size” for actual plans, side-by-side with the estimates.  There are many other plan metrics available by right-clicking any column header and selecting “Column Chooser.”
• Significant differences between estimates and actuals are highlighted.
• Possibly problematic operations like scans and bookmark lookups are highlighted.

Top Operations

Another one that is as it sounds, a simple list of all plan operations, sorted DESC by total estimated cost by default.  You can of course sort by any of the other columns, as well as group by operation type, object, etc. The same columns and highlighting as on the Plan Tree tab apply here.

Query Columns

This is one of the most useful views for many plans. What is shows is a list of all columns accessed by the query, for whatever reason (sarg, join, output list, sort), along with the table, operation, and index used. How is it useful?

Take the case of a bookmark lookup, where you have some columns that aren’t covered by the index chosen by the optimizer. The traditional ways of figuring out which columns aren’t covered is by hovering over multiple nodes on the graphical plan to determine the index used and columns accessed, clicking through the Properties pane for those nodes, or looking through a showplan_all, all of which can be tedious and error prone.

Instead, when you look at the Query Columns list, you’ll see the columns for related operations grouped together (indicated by a thick separator bar), with the index used and the columns in the lookup highlighted in orange:

From here it’s often a simple matter to make the appropriate index modifications to eliminate the lookup. In the case above, I can see that index [IDX_ClientID] isn’t covering columns [LEASE_ID] and [DOCUMENT_SIZE], so I may want to add them as included columns to the existing index, or create a new covering index.

NOTE: In the full version of SQL Sentry v6, you can directly open the index properties from this and all other plan tabs using context menus, or by double-clicking nodes on the graphical plan. This is something you used to be able to do in Enterprise Manager but was dropped in SSMS, much to my dismay. We wanted to make this available in the Plan Explorer as well since it can be a huge timesaver, but currently the tool has no integration with SSMS in order to keep the install simple.  Rest assured, if/when this changes we will add this capability to the tool.

This beta build has proven very solid, and handles all plans we’ve seen thus far, regardless of size or complexity.  However, as it is a beta, you may find a quirk or two.  

Special thanks the first beta testers who really hammered the tool and helped us get it where it is today! In particular, Jonathan Kehayias (blog | twitter), Mladen Prajdic (blog | twitter), Christian Bolton (blog | twitter), Brent Ozar (blog | twitter), Brian Kelley (blog | twitter), and Aaron Bertrand (blog | twitter) provided some great real-world plans and feedback.

There are many more features and use cases I’ll be covering in the coming weeks, so stay tuned…

I hope you enjoy using it as much as we’ve enjoyed building it!

Greg Gonzalez

Greg is founder and Chief Scientist at SentryOne. He is a Microsoft developer by background and has been working with SQL Server since the mid 90s. He is perpetually engaged in the design and development of SQL Sentry, Plan Explorer and other SentryOne solutions for optimizing performance on the Microsoft Data Platform.