SQL Sentry Plan Explorer

SQL Sentry Plan Explorer is a FREE tool which builds upon the graphical plan view in SSMS to make query plan analysis more efficient. It contains many of the plan analysis features introduced in SQL Sentry v6, however, the lightweight tool does not require a collection service or database.

Features

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.

The user interface is broken into three major sections:

* Statements Tree
* General Query & Plan Info
* Plan Details.

Statements Tree Section

* The Statements Tree represents one of the biggest differences between SQL Sentry 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, including operation counts.
* Click on any statement in the grid to be shown the associated plan for that statement only.
* Click on any control structure (IF or WHILE) or EXEC higher up the tree to see a filtered view of the plan starting from that point.
* Sort the statements list and the highest cost trees and statements will immediately bubble up to the top.
* So whether there are 10 or 1,000 statements in the master plan doesn't really matter, it's trivial to find the highest cost individual plans 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

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

Plan Details Section

* This section contains 4 tabs, each of which serves a different purpose:
o Plan Diagram
o Plan Tree
o Top Operations
o 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

* The Plan Diagram view will certainly be recognized by everyone familiar with SSMS, although you should immediately notice some differences. First, there is color! The cost information is color-scaled so you can quickly see which operations are heaviest, and Lookups and Scans are also highlighted.
* Right-click the diagram and context menus provide access to a variety of other functions. 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.
* 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 actual estimated rows or data size, and line width is scaled accordingly. (SSMS always shows 1 row for these operations in estimated plans)
* The mouse scroll wheel can be used for both scrolling up down, and zooming in/out (by holding Ctrl) !
* The optimized layout algorithm renders plans using much less real estate than SSMS, meaning you see more of the plan without having to scroll and zoom.

Plan Tree

* The Plan Tree shows all operations and associated metrics. On the surface it looks similar to a showplan_all, however it's different in several respects:
o You can expand and collapse sections, and sort within levels.
o 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".
o Significant differences between estimates and actuals are highlighted.
o Possibly problematic operations like scans and bookmark (key|rid) lookups are highlighted.

Top Operations

* A simple list of all plan operations, sorted DESC by total estimated cost by default.
* Sort by any of the other columns, as well as group by operation type, object, and other criteria.

Query Columns

* Shows 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.
* Columns for related operations are grouped together (indicated by a thick separator bar), with the indexes used and bookmark (key|rid) lookup columns highlighted in orange. This makes it easy to see how indexes can be modified to eliminate lookups.

For more details, check out the blog post on SQL Sentry Plan Explorer.

Software Download

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

SQL Sentry Plan Explorer x64

SQL Sentry Plan Explorer x86

SQL Sentry Plan Explorer Documentation

SQL Sentry Plan Explorer Change List

General discussion and support for the free tool are provided through the SQL Sentry Plan Explorer forum.