Wednesday, October 20, 2010

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:

big_plan_estimated

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:

big_plan_actual

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:

statements_tree

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

statements_tree_stmt_sel

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:

statements_tree_while_sel

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:

statements_tree_sorted

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:

query_info

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):

plan_diagram_color_scaling

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:

plan_diagram_costs_by_cpu

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:

plan_diagram_cumulative_costs

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_tall_ssms

Plan Explorer version:

plan_tall_pe

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:

columns_lookup

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.

Beta Downloads

You MUST HAVE .NET 4.0 PRE-INSTALLED to install and use the Plan Explorer. Download it here.

SQL Sentry Plan Explorer x64

SQL Sentry Plan Explorer x86

SQL Sentry Plan Explorer Documentation

Plan Explorer Change List

General discussion and support for the free tool will be provided through the SQL Sentry Plan Explorer forum

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.  If you run into any problems, or have any ideas, questions, etc., email me at greg at sqlsentry.net.  For questions, please be sure to check the forum and the documentation first.

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!

9 comments:

  1. Hello! Love your Plan Explorer from the first look ;)

    Especially the way it zooms plan diagram by Ctrl+Wheel. Just put the mouse pointer to what you want to zoom.

    I've just got one suggestion. Plan Explorer already shows warnings with exclamantion sign and can show details, such as missing index details.
    Just now I've stumbled with missing join predicate warning. According to http://scarydba.wordpress.com/2009/09/15/no-join-predicate/ that warning is very serious like "BY ALL THE GODS IN VALHALA, YOU DON’T HAVE ANY JOIN PREDICATES IN THIS QUERY!" :) because query optimizer produced carthesian product somewhere and it should be investigated ASAP. It was rather hard to find error sign on one of the boxes of the plan diagram, but I had known what to look for. So the suggestion is obvious: why not display all the warnings separately?

    Thank you.

    ReplyDelete
  2. Ilya,
    Thanks, glad you like it! Scrollwheel is actually one of my favorite features too ;-)

    This is a good idea, and something similar has already been considered for missing indexes, we just didn't have time to get it in there. You should see changes in this area soon.

    Btw, you probably noticed that we already use the red critical icon when there's a missing join predicate, making it much more obvious than SSMS which uses the yellow warning icon, esp when you have a parallelism indicator on the same node.

    Cheers,
    -greg

    ReplyDelete
  3. Greg,

    red critical icon is really better than SSMS yellow sign but only if you know what to look for :) A node that has missing join predicate not always has CPU/IO statistics to be first on operations top. May be add to top operations view a checkbox "Warning" and display all warnings as a hint?

    Another thing that may be useful or not is to jump from selected node on plan diagram view to that node on plan tree and top operations views and vice versa.

    With best regars,
    Ilya

    ReplyDelete
  4. Ilya,
    Thanks for the input regarding missing predicates.

    All 4 plan detail tabs are synchronized, so anything selected on one will be auto-selected on the others... all you need to do is switch tabs.

    Cheers,
    -greg

    ReplyDelete
  5. Oh, I haven't mentioned that tabs are in sync and you wrote about it already. Thank you.

    Best,
    Ilya

    ReplyDelete
  6. any plans to make this a plugin to SSMS instead of standalone app? big downside in my mind is that this can't simply replace SSMS impl of showplan. Or am I missing something?

    ReplyDelete
  7. It is covered in the documentation under Limitations that this is being considered. We currently provide extensive integration with SSMS with the full SQL Sentry software for various tasks, so we know well that it introduces a long list of additional dependencies and test scenarios to ensure everything works properly with all versions of SSMS. If we'd tried to get it into the initial release, it likely would have delayed it by several weeks or more, so we elected to wait on this.

    Until then, we've tried to make it easy enough to get plans in and out of Plan Explorer that hopefully having it is better than not ;-)

    ReplyDelete
  8. Can we have Jion Operator count on statement tree section?. right now it shows # index seek, scan.

    ReplyDelete
  9. Praveen,
    We do show counts for Hash joins by default if they exist since those can be higher impact. Right-click the column headers and select "Column Chooser" and you will see a column for each operation type used in the plan, including all joins. We don't show columns for all operation types by default as it would be TMI, only the critical ones.

    Please use the SQL Sentry forum link above for any additional questions or feedback. Thanks.

    ReplyDelete