September 13, 2011 | SQL Server

A demo kit for SQL Sentry Plan Explorer

UPDATE 2016-10-07 – This demo kit has been overhauled:

https://blogs.sentryone.com/aaronbertrand/plan-explorer-3-0-demo-kit/


Plan Explorer is a free application from SQL Sentry, designed to make execution plan analysis faster and easier. It was developed in response to customer demand for deeper query analysis, but was originally intended only for the paid version of the software. When the team realized how useful the tool could actually be, they decided to give it to the community for free. When I was deciding whether to join SQL Sentry or stay at my previous gig, this selfless gesture quite literally sealed the deal.

You can read more background about Plan Explorer in this blog post from Greg Gonzalez.

 

Download Plan Explorer

 

Before you get started, please make sure you have the most current version of Plan Explorer installed. You can always download the latest version and the companion SSMS Add-In from the following site:

http://www.sqlsentry.net/plan-explorer/

 

This Kit

 

This kit is designed to help you present Plan Explorer to your colleagues at the office, a SQL Saturday, or a local user group meeting. We have had multiple requests for some kind of demo to help people share the tool with their audiences without having to fabricate their own problem queries or interesting plans. So I put this post together in response to that demand and to help give people a jump-start on showing off the tool. That said, this guide can also be used to familiarize yourself with Plan Explorer even if you don't have an audience to present to…

 

You can download the kit from here:

http://bit.ly/PlanExplorerDemoKit

In this kit you will find a ZIP archive containing a very brief PowerPoint presentation and 7 sample plans. The plan files are named with successive numbers and descriptive names; the numbers just represent the order in which I present them. If you have better plans to showcase, by all means, do not limit yourself to the plans I’ve provided. Also, do not feel like you have to present the plans in the same order.

 

 

This Page

 

This page is intended to be a living, breathing guide on how to present Plan Explorer. Since it will be updated as we add new features to the tool, you should always check back here for updates. I think this will be a much more useful approach than including a Word document or PDF file in the kit itself.

 

When I’m presenting about Plan Explorer, I first walk through the limitations of plan analysis in Management Studio. This isn’t meant to knock SSMS or Microsoft or the SQL Server team, but it can’t help from sounding like it. After all, there really hasn’t been much development in this area since SQL Server 2000, unless you count the missing indexes recommendations or new operator icons. 🙂 Again, these are not jabs at the team or the product – just a symptom of focus being elsewhere, with the end result being that plan analysis isn’t as efficient as it could be. Most people familiar with execution plan analysis in Management Studio will already be aware of these weaknesses, but if you have a mixed audience, you may want to spend more or less time on these points. I won’t list them out individually up front, but I will call them out at key points in the description of the included plans below. (I’ll post a separate discussion about the weaknesses of Management Studio’s built-in plan analysis features.)

 

 

The Plans

 

1.ActualWide.sqlplan

 

I open this plan in Plan Explorer (in fact before I start I have all 7 plans open in Management Studio, and 7 instances of Plan Explorer open with each plan pre-loaded). To demonstrate the functionality of the add-in, you could just as easily load each plan in Management Studio, then right-click and select “View with SQL Sentry Plan Explorer.”

 

I point out several visual aspects, starting with the color. I ask folks what stands out to them, and invariably someone shouts out, “Red!” I point out how you can spot the expensive nodes pretty quickly – I can also point out that the operator costs are scaled by color (red -> yellow), and that certain operators are also highlighted below the operator icon (key lookups in orange, scans in yellow).

 

I show how we’ve optimized plan layout by pointing out that the horizontal space is re-used to reduce the amount of vertical scrolling. This plan isn’t fantastic for showing space efficiency because it’s wide, but the vertical impact is clear if you compare it to the same plan in Management Studio (these are the same 8 operator nodes, taking up much more space in Management Studio):

Next we can move to the Plan Tree tab. This is a text-based diagram similar to the familiar SET SHOWPLAN_TEXT mode, however it comes with loads of additional data. You’ll notice that, like the graphical plan, key lookups are highlighted in orange, and scans are highlighted in yellow. You’ll also see highlighting on the Actual Rows / Est Rows columns, when the difference exceeds a certain threshold – if you hover over that space it will tell you the variance and suggest that there may be a statistics issue. This can be helpful in quickly identifying potential issues by highlighting significant differences in actual vs. estimated:

 

 

The Top Operations tab shows the same data as the Plan Tree tab, but now the data is sortable. So you can order by actual executions, highest first, and see that perhaps that key lookup is something you want to look deeper into. There is a LOT of data on these two tabs, and I don’t want to influence what you want to focus on – if there are metrics here that you typically use to help guide performance troubleshooting, please feel free to focus on those. Also don’t forget to right-click any column header and look at the Column Chooser… there are many more metrics available that we don’t show in the UI by default. If you want to add any metric to the column list, you can simply drag it from the Column Chooser onto the column header:

 

 

I use the Query Columns tab to show one thing – the key lookup on the table PerformanceAnalysisPlanOpTotals. By highlighting the non-covered columns that are fetched via the lookup, we provide a much quicker way to figure out either (a) what columns to add to they key or include list of the index, (b) to drop those columns from the query (since often these are the result of SELECT * or ambitious/aggressive column inclusion), or (c) to just leave the query alone:

 

 

 

If time is flying you can view the lookup on PerformanceAnalysisTraceCachedPlanItems; this looks like a simple case where you might add the CausedRecompilation column as an INCLUDE column rather than change the index key, but if you scroll over to the filters column, you will see that it is used in a filter predicate. This information can help guide whether adding it as an INCLUDE column really does makes sense (it may be okay in some cases, but it is certainly a valid data point to consider).

 

 

You may want to show the Parameters tab, where you can rule out parameter sniffing in this case – since the compiled and runtime parameter values are equal – but point out that you could sniff out such discrepancies here.

 

You can also point out that when you have an actual plan, you can quickly and easily switch between the actual and estimated plan, without having to re-generate them, by simply clicking the following button on the toolbar:

This lets you spot major differences between the two versions of the plan, whether it be which node is the most expensive, the movement of data between operators, or the types of joins or operators involved.

 

Finally, you can show the Text Data tab in the middle, and scroll around to show how much more of the statement we can see, that it is syntax highlighted, and that it is much easier to read than the tooltip in Management Studio. Now as it turns out, the XML node that hosts the statement text is limited to 8k, so we truncate the statement as well – but we show many more complete statements that way. Ultimately, though, we can only show you the information that is in the plan.

 

 

2.EstimatedTall.sqlplan

 

I open this plan in Management Studio, and zoom to fit. I usually make a joke about challenging anyone in the audience to find the most expensive node within one minute. This isn’t really a fault of Management Studio, but rather of the plan itself.

 

Then I open the plan in Plan Explorer. I admit that there are certainly some plan shapes that we can’t make look good, either, and that finding the most expensive node visually in Plan Explorer isn’t any easier in this case.

 

But this is where the power of the Statements Tree comes in. Forget about the graphical plan and move to the Statements Tree node. Click on the “Est Cost” column header to sort by that column, and highlight the row with the 33.2% cost:

 

 

Boom, you’ve found your problem statement, and now you can focus on the graphical plan for just that statement. You don’t have to tune this any further; it’s just to demonstrate how quickly you can focus in on the important parts of even an extremely complex batch, and ignore the rest of the noise.

 

 

3.ParallelSeek.sqlplan

 

I show this plan in SSMS, explaining that a user complained about this query – it's suddenly much slower than it used to be. Nothing on the graphical plan in Management Studio stands out to me – you can ask the audience if there is anything obvious to them. They might ask to see the tooltips on each operator, and they may even spot the difference between estimated and actual rows on the Nested Loops or Index Seek nodes, but you can point out how cumbersome this can be to try and track that down.

 

With Plan Explorer you can reinforce how much quicker you can get to data that is hidden in properties panels and/or not immediately obvious in Management Studio. When you open the plan you can immediately see the expensive red node:

This might also be a spot to show a couple of the ways we can change the appearance of the data:

  1. Right-click the plan and change Costs By > to I/O – you immediately see that the most expensive node switches from the Sort operator to the Index Seek:

  2. Right-click again and change Line Widths By > to Data Size. You can point out the difference in the thickness of the arrows, and also that the indicators above/below the lines change to MB instead of row counts.

But perhaps more importantly, we can switch to the Plan Tree tab and immediately see a cause for concern – on the rows that show the Nested Loops and Index Seek operators, we can see that parallelism was intended, but the work was not evenly distributed across threads. While this can sometimes be attributed to spills to tempdb and other causes, in this case we can clearly see the orange highlighting that shows a huge discrepancy between estimated and actual… which means this parallel plan gone wrong is most likely due to out-of-date statistics:

 

4.CorrectPercentages.sqlplan

 

This plan is just used to demonstrate a bug in Management Studio where the math doesn’t always work – if you work the plan from right to left, top to bottom, you can count out the percentages and add – 16 + 62 + 11 + 14 + 11 + 1 + 11… you quickly get way over 100%. You don’t necessarily need to show this plan in SSMS, but you can just ask a question like, “Has anyone ever seen a plan in SSMS not add up to 100%?” If you don’t get some nods or raised hands, you might not be at a SQL Server event. (For more info see Connect #267530 – closed as "by design" and a few others such as Connect #370798 and Connect #621330.)

 

If you open the same plan in Plan Explorer, the math actually adds up, and you can repeat the spot checks – or just tell them to trust you that it adds up to 100%. This might also be a good plan to demonstrate the costs by cumulative cost rather than by individual node – if you right-click the plan and choose cumulative costs, you will see that the color scale changes from reds to yellows to blues. With this you can follow sub-trees to see the path the most expensive costs take, and you can see that it’s different if you switch between CPU and I/O.
 

 

5.NestedViews.sqlplan

 

This plan shows some operations involving nested views, where it isn’t always easy to wade through all the hierarchy and land at the actual base tables involved.  In Plan Explorer, open this plan and move to the Join Diagram tab. You may want to expand the area vertically or pre-arrange things before the presentation for the most optimal layout. You’ll see what resembles a database diagram in Management Studio, but it is showing only the join columns in each base table that are used, with arrows to illustrate the relationships. This is much easier than trying to piece together the views and their underlying tables as shown in Management Studio:

 

 

 

6.MissingIndex.sqlplan

 

In Plan Explorer, the missing index is suggested only on the appropriate plan – in current versions of Management Studio, there are scenarios where the same index is recommended on every plan in the batch, even statements that don’t involve the table in question (and some that don’t involve any tables at all – see Connect #518467 for more details).

 

In the Statements Tree tab, we automatically add a column to show Missing Indexes if any of the plans contains information about an index suggestion. For this plan, you will see a row that has a value of 1 in this column:

 

 

If you highlight that row, then look down at the graphical plan, you can see the little yellow exclamation mark / warning sign on the lead node. If you hover over that you will see a warning at the bottom that mentions missing indexes; right-click the node and choose “Missing Index Details…” to show the CREATE INDEX statement.

 

 

You’ll note that we generate the exact same CREATE INDEX statement that Management Studio does, but we add a little extra disclaimer that points out that just because adding an index will help this query, does not mean it’s a good idea overall – adding or dropping indexes should never be entertained solely in the context of a single query but rather with a healthy knowledge of all the objects and queries it will affect over a full business cycle.

 

 

7.RowsPerExecution.sqlplan

 

This plan shows a case where we auto-correct rows and cost information for lookup and spool operators. If you compare this plan in Plan Explorer and Management Studio, you will see that while SSMS gets the total operator costs right (disregarding the incorrect percentages, of course), in the tooltips you will see that it does not extrapolate the number of executions correctly and, therefore, total number of rows, estimated CPU and estimated I/O costs are all wrong.

 

Here are the properties for the most expensive Merge Join operator in SSMS and Plan Explorer. You will notice a significant different in the estimated I/O cost for the operator, meaning if you were trying to focus on I/O alone, you might ignore this node altogether if you were looking in Management Studio:

 

 

 

And here are the properties for one of the Key Lookup operators, which demonstrate numbers off the mark for estimated CPU cost, estimated I/O cost, and number of rows:

 

 

These differences are also well illustrated on the Query Columns tab in Plan Explorer.

 

 

This is not an existing plan

 

If you have an instance of SQL Server on your presentation machine, you can illustrate the fact that Plan Explorer can derive runtime metrics without a lot of work. Take a query, that can be as simple as:

 <blockquote>    SELECT * 
    FROM       sys.all_objects AS so     INNER JOIN sys.all_columns AS sc     ON so.[object_id] = sc.[object_id];

Run the query and generate an actual plan from within Management Studio. Observe the data you get back with that, and ask people how they might capture duration, CPU and I/O runtime metrics to go along with this (the answers will likely be SET options or profiler).

 

Then open a new instance of Plan Explorer, paste the same query into the Command Text window, and click on the “Actual Plan” button on the toolbar. There is a warning (more on that below); once you click OK on the warning, you will be prompted for connection credentials; provide them, and hit Enter.

 

When the query has completed, you’ll see the plan diagram, but up in the statements tree you’ll see some columns that were previously empty – you can now collect Duration, CPU, Reads, Writes, etc. without explicitly setting those options and without having to run a profiler trace to capture them. This shows how you can make minor adjustments to the query, and immediately see how it impacts the plan, these runtime metrics, or any of the other data we show on the various tabs.

 

More about the warning: you’ll want to point out that Plan Explorer is not a query tool, and as such, while the query is executed at the server, the results are simply discarded. To reinforce a point that Kalen brought up in the comments, this includes all DML queries – INSERT, UPDATE, DELETE, MERGE as well as SELECT – so you should consider generating an actual plan identical to running the same query in Management Studio, without the output. You can turn off the warning, using the Help menu option, "Warn on Actual Plan Execution."

 

 

Further Exploration

 

When I’m giving this presentation as part of a vendor session, I usually go into the integration with SQL Sentry Performance Advisor – this adds a ton of additional functionality, such as automatic plan collection with Top SQL, allowing you to look at plan differences over time, correlation of the entire call stack, side effects such as auto-stats (StatMan calls), and various other things including the fact that you can work from a problem down to investigating the plan at the time without having to have generated the plan first – if the plan has changed, in real time, you don’t have that capability unless you were actively capturing it at the time. But I realize this isn’t a sales pitch – if you want a more substantial demo that includes information about how plan analysis works in Performance Advisor, we’re always available to help out. 🙂

 

I do plan to extend this demo kit over time, or to create new and separate entities altogether – ideally I'd like to focus on known databases, such as AdventureWorks, and step through at a very low level the process of getting from problem detection to resolution.

Questions? Comments? Other Feedback? Please let me know at abertrand@sentryone.com

 

10 comments on this post

    • Kalen Delaney - September 14, 2011, 12:58 AM

      Hi Aaron, thanks for letting us know about this!
      Do you give us the queries to run and the data, or just the plan?
      What if the plan we're analyzing is a data modfication query… when it 'runs on the server' does that mean it's actually changing our data?
      Thanks again!
      Kalen

    • AaronBertrand - September 14, 2011, 1:07 AM

      Hi Kalen,
      I supply only the plans, not the queries (well, the queries are included as part of the plan XML, and we show them on the Text Data pane, but they do you little good without the schema behind them – plus the XML doesn't always incldue the whole query for longer statements). The issue is that some of these plans were derived on systems where it would be quite tedious to try to reproduce schema for public consumption. I do plan to put something together with more "public" databases like AdventureWorks (my problem there is there are 40 different versions of AdventureWorks – so I imagine I will be developing my own).
      The query always runs on the server if you generate an actual plan – this includes data modification queries. It is just like executing the query from Management Studio, except you don't see any output.

    • Justin Randall - September 14, 2011, 5:48 PM

      Aaron, This is outstanding stuff.  Thanks for the excellent presentation.
      Justin

    • Simon - September 16, 2011, 1:05 PM

      Well done to the SQL Sentry team on an amazing tool. I wish I could have something like this for Oracle! (hint hint!) Actually, I wish I had the native SSMS Plan Viewer for Oracle!

    • Shy Engelberg - September 20, 2011, 6:31 PM

      Aaron, the kit is great, it has everything one need to present the tool.
      This tool changed the way I analyze execution plan and i present it in every session.
      Thanks.

    • Kalen Delaney - October 6, 2011, 6:50 AM

      Aaron
      It seems that when I install the Plan Explorer that the file association for .sqlplan is automatically changed to open up the Plan Explorer. Did I miss something during the installation? It was a real pain to get the default to go back to SSMS.  But Plan Explorer is still a great tool, I just want the option when I teach a class to just use the Microsoft-supplied tools.
      Thanks
      Kalen

    • AaronBertrand - October 6, 2011, 4:40 PM

      Hi Kalen, Plan Explorer does not inherit the file association for .sqlplan files unless (a) you left the "Always use the selected program" checkbox checked when opening a .sqlplan file or (b) you installed Plan Explorer before Management Studio. If neither of these are true, can you please detail the version of Windows, version of SSMS, version of Plan Explorer, order of installation, and any relevant actions you took.
      If Plan Explorer is currently the default handler (regardless of how it got that way), it is actually quite easy to change back (just like changing any file type association) by selecting any .sqlplan file in Windows Explorer, Shift+Right-Click > Open With… > Choose Default Program, select Management Studio, and make sure the "Always use…" checkbox is checked. The details may vary slightly if you're on XP for example, as some of the wording has changed, but the process should be quite similar. If you opened Regedit to "fix" this, I feel your pain, but it shouldn't have been necessary.

    • Kalen Delaney - October 7, 2011, 12:29 AM

      SSMS was already installed, and the first .sqlplan file I went to after installation already was associated with Plan Explorer.  I did have Plan Explorer already open at the time.
      And it wasn't all that easy to change back because SSMS wasn't in any list and to find the executable for it took me a while.
      Win 7 Pro SP1
      SSMS 10.50.1617.0
      PE 1.1

    • Kalen Delaney - October 7, 2011, 12:42 AM

      Hi Aaron… and another question… how do I get the functionality to right-click a plan in SSMS and get the option to open in Plan Explorer?
      Thanks!
      Kalen

    • AaronBertrand - October 7, 2011, 12:54 AM

      Kalen,
      If you used Shift+Right-Click on a .sqlplan file, and Management Studio wasn't in the list, then it sounds to me like the file associations were already messed up prior to installing Plan Explorer. We'll only assume the file association if one doesn't already exist, and in theory that should already be the case if you had SSMS installed. The fact that it didn't show up as an option leads me to believe there was a pre-existing issue.
      To get the SSMS add-in functionality, you need to install the SSMS add-in. We package this as a separate install from Plan Explorer because it requires an SSMS restart, and since the actual functionality is relatively simple (therefore not prone to many changes), we didn't want to force an SSMS restart every time we updated Plan Explorer (which we envision will have plenty of enhancements over time). You should download the latest build of Plan Explorer first, then download the SSMS add-in from the right-hand column here:
      http://sqlsentry.net/plan-explorer/sql-server-query-view.asp
      I also talked about the add-in here:
      /blogs/aaron_bertrand/archive/2011/08/03/sql-sentry-plan-explorer-new-release-and-an-ssms-add-in.aspx

Comments are closed.