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!

Monday, October 18, 2010

Aaron Bertrand Joins the SQL Sentry Team

I'm pleased to announce that highly regarded SQL Server MVP, Aaron Bertrand (blog | twitter), will be joining the SQL Sentry team!  His first day on the job will be next Monday, October 25th.  We are all extremely excited to have him on board, for a variety of reasons.  On the surface, it may appear to be an attempt to "keep up with the Jones'" in the sense of us bringing on a high profile community advocate, but this is actually much more than that.  Aside from Aaron's vast knowledge and experience with SQL Server, having known and worked with him for several years now, he is a person that just gets things done.  Sure, he has phenomenal communication skills and will undoubtedly continue blogging and speaking about SQL Server, but for us the move is just as operational in nature.

I mean, who better for a SQL Server ISV like us to have in house than the person who has far and away provided the most feedback to Microsoft on SQL Server itself? (see Connect stats lower right)

Aaron was a long time user of SQL Sentry software in his previous job, so he already knows our software inside and out and has provided lots of great feedback over the years.  The SQL Server environment at OTOLabs is unique – high volume OLTP systems with many hundreds of databases – it definitely stressed our software early on and pushed us to come up with ways of dealing with that kind of scale.

If you know Aaron, you know that he says what he believes and doesn't pull any punches.  He's exactly the kind of person we want here, someone with unquestioned integrity who isn't afraid to tell it like it is, and who will continue to look at our software with a critical eye.  I have no doubt that Aaron will have a significant impact on our company and our software in many different ways, some we can anticipate, but many we probably can't.

I could go on, but from here I'll defer to Aaron's post about the move.

Welcome aboard, Aaron!