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/sql-server-query-view.asp
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.
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:
- 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:
- 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:
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@sqlsentry.net