Aaron Bertrand

An updated Plan Explorer demo kit

Free eBook : Query Optimization
SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

Paul White is an independent SQL Server consultant specializing in performance tuning, execution plans, and the query optimizer.

Paul’s Posts

 

The demo kit has been updated for Plan Explorer v3.0 and to use the WideWorldImporters sample database going forward. Please update your bookmarks to the new URL:
 

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

 
Back in 2010, we first released Plan Explorer, a free application for query tuning through deep but intuitive execution plan analysis.

The following year, I created a demo kit for Plan Explorer, responding to numerous requests for demos to help people share the tool (without having to come up with their own problematic queries and interesting plans). The kit was designed to give people better insight into the advantages of our tool, and to help them share its virtues with colleagues, user groups, and the SQL Server community at large. Of course, it can also be used to familiarize yourself with the application, even if you have no plans to present it to others.

That demo kit is a little long in the tooth, uses various databases that would be cumbersome to reconstruct, and is missing several Plan Explorer features that have been introduced in the meantime (as well as a paid, PRO edition). Rather than overhaul that post, I thought I would simply provide a completely fresh kit closer to home (only borrowing a little from the old kit), and using the AdventureWorks2012 sample database from Microsoft, which anyone can access and therefore reproduce the same plans I'm providing.

If you don't have AdventureWorks installed, or you don't have SQL Server 2012, don't worry; Plan Explorer will still be able to open all of the plans included here, and I have validated that they all open in older versions of Management Studio as well (going as far back as 2008).

You can download the kit here – it contains all of the files explained below, as well as a simple PowerPoint presentation you are welcome to modify to suit your needs:

Before you start, you should make sure you have the most recent version of Plan Explorer installed (download here), which has an SSMS add-in that presents this menu option in the context menu for any execution plan:

Context menu from SSMS Add-In
Context menu from SSMS Add-In

For a fantastic video-based tutorial, you may want to check out this PluralSight course from Jonathan Kehayias (@SQLPoolBoy): SQL Server: Using SQL Sentry Plan Explorer. A subscription is required, but I can assure you it is a resource worth checking out.

When I present about Plan Explorer, it's hard not to talk about the limitations in Management Studio. After all, these are the very reasons an opportunity for our tool existed. In spite of how it sounds, this isn't meant to knock SSMS or Microsoft or the SQL Server team; it's just the reality that this set of features has been largely ignored since SQL Server 2000. How much time you want to spend on these points will depend on the level of your audience.

I also like to make it clear that, while we can present some of the information in a clearer way, we are ultimately dependent on the quality of the data that we get from SQL Server or the plan. Cost percentages, in particular, are always estimates, even when generated with an actual plan from Plan Explorer – so please caution people to not make decisions based solely on cost percentage numbers presented to you anywhere.

Table of Contents

Demos Involving FREE Features

Below are a few plans I use to demonstrate the various features of Plan Explorer FREE. Many plans include multiple versions: either .queryanalysis or .pesession, which enable features only possible in Plan Explorer, and then another version using .sqlplan (so you can always open up the plan in Management Studio).

    Nested_Subquery

     
    This demo uses the files Nested_Subquery.queryanalysis and Nested_Subquery.sqlplan.

    This is not exactly a model query; don't blame me, it came straight from Microsoft's SELECT Examples topic in Books Online. Still, it showcases several of the features of Plan Explorer quite nicely.

    SELECT DISTINCT pp.LastName, pp.FirstName 
    FROM Person.Person pp 
    JOIN HumanResources.Employee e
    ON e.BusinessEntityID = pp.BusinessEntityID 
    WHERE pp.BusinessEntityID IN 
    (SELECT SalesPersonID 
       FROM Sales.SalesOrderHeader
       WHERE SalesOrderID IN 
       (SELECT SalesOrderID 
          FROM Sales.SalesOrderDetail
          WHERE ProductID IN 
          (SELECT ProductID 
             FROM Production.Product p 
             WHERE ProductNumber = 'BK-M68B-42')));

    In Management Studio, this generates the following graphical plan. The first thing I like to do is show how space inefficient Management Studio is; I've highlighted all of the wasted space for this plan in pink. Wasting space and spreading things out due to a sub-optimal layout algorithm just means less can fit on screen (or, to make it fit, everything has to be a lot smaller than it probably could be):

    Nested_Subquery plan in Management Studio, with pink highlighting the wasted space
    Nested_Subquery plan in Management Studio, with pink highlighting the wasted space

    This same plan in Plan Explorer is laid out in a much more compact manner, meaning that more of the plan is displayed on screen and is more legible as a result:

    The same Nested_Subquery plan in Plan Explorer
    The same Nested_Subquery plan in Plan Explorer

    Some additional things that should be immediately obvious when looking at the graphical plan (especially when compared to Management Studio's representation):

    • Color! We highlight things for you to draw your eyes to the important pain points. We color the most expensive operator(s) with red, then scale down through orange and yellow. We also show things like scans with a yellow background.
       
    • We show the number of rows being exchanged between operators above the arrows between those operators. This makes it easier to understand the relative magnitude of different arrow widths without having to hover over them to read the tooltips. You can also right-click and select Line Widths By > Data Size (MB) to make the arrow widths and numbers adjacent to the lines show data size instead of row count. A quick example on part of this plan:
       

      Plan with Line Width By Data Size (MB)
      Plan with Line Width By Data Size (MB)

    • We moved the estimated cost percentage above the icon instead of below; this makes it far easier to spot with less eye movement (since the text below the icons can vary in size).
       
    • In Management Studio, the estimated cost percentages below the icons are shown as a combined cost of CPU and I/O. This isn't very helpful if you are investigating a plan specifically for CPU or specifically for I/O. Out of the box, we show the same calculations as SSMS, but we do allow you to change this through the context menu item Costs By > I/O or Costs By > CPU. Using this plan you can easily demonstrate how different those individualized costs can be:
       

      Plan showing costs by CPU vs. costs by I/O
      Plan showing costs by CPU vs. costs by I/O

    • We provide an option in the right-click menu to Show Full Object Names. By default, in order to conserve horizontal real estate, we show truncated versions of the names, similar to Management Studio. The option will sometimes sacrifice horizontal real estate, forcing the full object names to be displayed.
       
    • With an actual plan, you can switch between estimated and actual costs by using the Show Estimated Plan button on the toolbar. This can be used to quickly observe any operators where, for example, the estimated number of rows or data size differ substantially from the actuals. We highlight large discrepancies in other ways, too (more on this soon).
       

    In Management Studio, there isn't a whole lot more to a plan than the graphical output. Sure, you can get a lot of information from the tooltips, but you can only hover over one operator or exchange at a time, making it difficult to compare attributes between multiple items. You can also use the Properties panel, but this too is context-sensitive, showing you query properties, general plan properties, or operator properties, depending on where you have focused your mouse.

    In Plan Explorer, we show a whole lot more about what has gone on during execution, and try to make it very discoverable and easy to compare.

    Let's start with the Statements Tree, up in the Results tab. When you generate an actual plan from within Plan Explorer, this grid includes a host of runtime metrics per statement, including reads, CPU and duration. These are metrics you would only be able to obtain in Management Studio by also turning on things like SET STATISTICS TIME ON; and SET STATISTICS IO ON;.

    Statements Tree showing useful runtime metrics
    Statements Tree showing useful runtime metrics

    Don't see what you need there? Several columns will only appear if at least one statement in the batch has generated values there (Missing Indexes, for example, will only show up if at least one statement triggered a missing index recommendation). However, you can add any column that isn't presented by default; just right-click any column header and select Column Chooser. You will have a list like this; just drag any column onto the grid:

    Demonstrating adding columns from the Column Chooser
    Demonstrating adding columns from the Column Chooser

    You can also sort any column in this pane, so that if you have generated plans for multiple statements, you can quickly arrange them by reads, estimated cost, or any other column. This is far more manageable than the fixed output of multiple plans in Management Studio.

    Going down to the middle pane, you can see the query text for the currently selected statement – arguably an easier way to read the query than looking at the grid above. It is also fairly trivial here to get a good look at the XML without saving it separately; just switch to the Plan XML tab:

    Full Showplan XML is available in the Plan XML tab
    Full Showplan XML is available in the Plan XML tab

    If you look down to the status bar, there is also some useful information there, including server/instance, database, login, compile time, execution time, and actual rows:

    The status bar has some interesting runtime information, including compile time
    The status bar has some interesting runtime information, including compile time

    Next we can take a look at a couple of the grids in the lower pane. The Plan Tree tab is roughly the equivalent of SET SHOWPLAN_TEXT ON;, but much more digestible. We highlight operations such as scans and key lookups here, and we also highlight when there is a significant discrepancy between actual and estimated rows (hovering over any such cell will yield the following tooltip):

    The Plan Tree tab highlights things like scans and statistics discrepancies
    The Plan Tree tab highlights things like scans and statistics discrepancies

    Next we can move to the Top Operations tab. This is quite similar to the plan tree, but now the data is sortable. So, if you want to sort by highest estimated cost, or largest data size, or any other metric on the grid, you can quickly identify the most expensive operators in terms of that metric. While we pride ourselves on making more of a plan visible on screen, we can't cater to every single plan layout; switching to the grid and sorting can make it much faster to find the node by metric, or just identify all of a certain operator (e.g. all clustered index scans or key lookups).

    The Top Operations tab takes everything from the Plan Tree tab and makes it sortable
    The Top Operations tab takes everything from the Plan Tree tab and makes it sortable

    These grids, too, have a Column Chooser, so that you can add any of the many columns that we don't present by default. (Click here to see the full list for the Top Operations grid.)

    One other interesting thing to mention about these grids is that the operator selection is context-sensitive. So, for example, you can see that in the Top Operations tab, I've selected the most expensive operator (at least according to estimated combined CPU and I/O cost); if I move back to the plan diagram, it is the operator selected (and highlighted with a green dashed outline):

    Selected items remain selected when moving between tabs
    Selected items remain selected when moving between tabs

    This outline is quite visible, even when zoomed way out. This makes it very easy to pick an operator from one of the grids, and focus in on it immediately in another grid or in the graphical plan, regardless of how complex or large the plan may be. It works the opposite way as well; highlight an interesting operator in the graphical plan, then move to a grid, and that's the operator that is highlighted there as well.

    I'll talk about some of the other tabs here when we move on to different plans.

    Missing_Index

     
    This demo uses the files Missing_Index.queryanalysis and Missing_Index.sqlplan.

    This query did not come from Microsoft's samples, but is one I cooked up trying to create a plan containing both a key lookup and a missing index recommendation. This is not something you usually try to do, of course.

    SELECT soh.SalesOrderID, sod.ProductID, sod.OrderQty, sod.UnitPrice
    FROM Sales.SalesOrderDetail AS sod
    INNER JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    WHERE soh.TotalDue > 500
    AND sod.ProductID = 767
    AND sod.OrderQty < 5;

    This plan shows that the biggest cost is the Key Lookup against SalesOrderDetail. There is also a missing index; can you spot it? We show the missing index notification a little differently from Management Studio, with a warning icon on the first node:

    Plan showing Missing Index warning
    Plan showing Missing Index warning

    You could also point out here that the Missing Indexes column is now introduced in the Statements Tree grid:

    Statements Tree showing runtime metrics and the dynamic column Missing Indexes
    Statements Tree showing runtime metrics and the dynamic column Missing Indexes

    The tooltip tells you that at least one missing index was found, and offers you to right-click to see details:

    Tooltip and context menu for SELECT operator with Missing Index
    Tooltip and context menu for SELECT operator with Missing Index

    When you do, you'll see a new window pop up. You might notice we have a stern disclaimer about not blindly creating indexes just because a plan told you to do so (much more detail in this blog post):

    Missing Index information, including disclaimer about creating indexes in a bubble
    Missing Index information, including disclaimer about creating indexes in a bubble

    One other thing I wanted to point out in this plan is how we represent key/RID lookups. When you have a lookup, this usually means that you probably have an index that *almost* satisfies the query, but one or more columns actually have to be retrieved from another location (usually the clustered index).

    If you move to the Query Columns tab, you can see that every column used in the query is listed here (whether the column is used for output, sorting, or filtering). And you'll see that OrderQty and UnitPrice are retrieved using a key lookup:

    Query Columns tab, highlighting columns requiring a key lookup
    Query Columns tab, highlighting columns requiring a key lookup

    Often a key lookup is an indication that you should consider either adding this column to the index, or dropping it from its use in the query (sometimes columns are included for testing purposes and left in, or otherwise later deemed to not actually be needed). However, we show other attributes here as well, to help you make a more informed decision about if and how you should change any indexes. For example, knowing whether the column is used solely for output without any filtering can probably lead you to using an INCLUDE column, whereas seeing that it *is* used for filtering (or sorting) might lead you to add that column to the key, when possible (or perhaps even consider a filtered index).

    In this case, since we see that OrderQty is involved in a filter, it is not very likely that this column should be added as an INCLUDE column in order to eliminate the key lookup – probably better as a key column or as the predicate of a filtered index (though only if this value is frequently used in the filter).

    These are not decisions that should be made exclusively based on this screen, though, and I can't stress it enough: index changes should be weighed in the context of your entire workload, and considering all queries, not just to help speed up any single query.

    Joined_Views

     
    This demo uses the files Joined_Views.queryanalysis and Joined_Views.sqlplan.

    This query just demonstrates how quickly reverse engineering a query can get out of hand when views are involved. Sometimes you just want to know what tables are being joined without having to go look at the definition of all of the views involved.

    SELECT p.BusinessEntityID, p.FirstName, p.LastName, p.JobTitle,
     p.EmailAddress, p.SalesQuota, p.SalesYTD, p.City, r.StateProvinceName,
     r.StateProvinceCode, r.CountryRegionName, r.TerritoryID
    FROM Sales.vSalesPerson AS p
    INNER JOIN Person.vStateProvinceCountryRegion AS r
     ON p.StateProvinceName = r.StateProvinceName;

    In Management Studio, the plan looks like this, when zoomed to fit:

    Plan for a seemingly simple JOIN in Management Studio
    Plan for a seemingly simple JOIN in Management Studio

    Admittedly, we can only cram so much information onto the screen as well:

    The same plan in Plan Explorer
    The same plan in Plan Explorer

    The table names are much more legible, but the plan shape still does not lend itself to understanding the relationships between the tables involved. Enter our Join Diagram tab, which generates a diagram so that you can see these relationships visually:

    Join Diagram showing the actual relationships in underlying views
    Join Diagram showing the actual relationships in underlying views

    Hovering over the connector lines show the type of join made and on which column(s); also, you can drag the tables around the diagram to make things more clear.

    This is probably as good a time as any to introduce the Table I/O tab, which tells you how many scans were performed against each table, and how many pages were read in various operations. What advantages does this have over SET STATISTICS IO ON;? Legibility is one; it is much easier to parse a grid than a bunch of plain text output. The other is the ability to sort (notice that I've ordered the I/O by logical reads):

    Table I/O tab showing sortable output similar to SET STATISTICS IO
    Table I/O tab showing sortable output similar to SET STATISTICS IO

    Parameter_Sniffing

     
    This demo uses the files Parameter_Sniffing.queryanalysis and Parameter_Sniffing.sqlplan.

    This plan demonstrates how you can see if runtime parameter values differ than those compiled with the plan, which can sometimes explain why a query suddenly behaves slowly (for the most clear-cut intro to parameter sniffing I've come across, see Bart Duncan's 2006 blog post). We're going to run two versions of a query here, in separate batches:

    -- first run this one:
    --EXEC dbo.uspGetBillOfMaterials @StartProductID = 2, @CheckDate = '20050101';
     
    -- then, in a separate batch, run this one:
    EXEC dbo.uspGetBillOfMaterials @StartProductID = 787, @CheckDate = '20050101';

    If we move to the Parameters tab, the difference between the compiled and runtime values – which *can* have a dramatic impact on cardinalities – is quite obvious:

    Parameters tab, showing differences in compiled and runtime parameter values
    Parameters tab, showing differences in compiled and runtime parameter values

    If you find that you already have a plan compiled for this query, you can clear it with the following code:

    USE AdventureWorks2012;
    GO
     
    DECLARE @sql NVARCHAR(MAX) = N'';
     
    SELECT @sql += N'
      DBCC FREEPROCCACHE(' + CONVERT(VARCHAR(255), plan_handle, 1) + ');'
    FROM sys.dm_exec_cached_plans AS p
    CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
    WHERE t.objectid = OBJECT_ID('dbo.uspGetBillOfMaterials');
     
    EXEC sp_executesql @sql;

    Parallelism

     
    This demo uses the files Parallelism.queryanalysis and Parallelism.sqlplan.

    In general, parallelism is a good thing, but SQL Server can sometimes lose its way. This is *usually* due to bad estimates, but not always. One thing we like to point out in parallel queries is the distribution of rows, per operator, across threads. Let's take the following query:

    SELECT TOP (5) h.SalesOrderID, h.TotalDue
    FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.SalesOrderDetail AS d
    ON h.SalesOrderID = d.SalesOrderID
    ORDER BY h.TotalDue DESC
    OPTION (QUERYTRACEON 8649);

    This uses the undocumented trace flag 8649, which attempts to force the use of parallelism on a query by lowering the cost threshold to zero. Ironically, this trace flag has been "documented" by Microsoft, in Connect #714968. That does not mean that I condone it for use in production code. :-) Another way you can simulate this is using DBCC OPTIMIZER_WHATIF(), as described by Sebastien Meine.

    Plan Explorer generates the following plan for this query:

    Parallel plan in Plan Explorer
    Parallel plan in Plan Explorer

    But much more interesting is the Plan Tree tab, which shows row distribution across threads:

    Plan Tree tab, showing how rows are processed per thread for each operation
    Plan Tree tab, showing how rows are processed per thread for each operation

    In my case I ran this on a quad-core machine, and we can see a slight imbalance in the threads. In a real-world scenario, this kind of thing can cause multiple threads to finish their work, then sit around waiting for other threads (with a higher number of rows to process) to finish. The threads that are waiting build up CXPACKET waits, which in turn can cause people to resort to drastic measures, like setting server-wide MAXDOP to 1. Paul Randal (@PaulRandal) discusses this a bit in a recent survey, What is the most worrying wait type?

    Another scenario described by Microsoft (and in which they tell you to open the .sqlplan file in a text editor and look through the XML!) is where parts of the plan – even though the operator node indicates parallelism – will actually execute in serial. The above grid would highlight that type of imbalance as well, by showing all activity on a single thread. Just be sure you know which operators can't go parallel – see Parallel Query Processing (TechNet) and this blog post by Paul White (@SQL_Kiwi).

    Update_Plan

     
    This demo uses the files Update_Plan.queryanalysis and Update_Plan.sqlplan.

    I also like to show one additional thing we reveal for plans that INSERT, UPDATE, DELETE or MERGE: how many non-clustered indexes are affected. Here's a query that updates a defined set of orders in a non-destructive way:

    DECLARE @t TABLE(so VARCHAR(32));
     
    INSERT @t VALUES('SO56287'),('SO56288'),('SO56290');
     
    UPDATE h SET 
      OrderDate = DATEADD(DAY, 0, OrderDate), 
      CustomerID = CustomerID * 1,
      SalesPersonID = SalesPersonID + 0
    FROM Sales.SalesOrderHeader AS h
    INNER JOIN @t AS t ON h.SalesOrderNumber = t.so;

    If you generate a plan (and then move to the second statement, since the first one is just the insert into the table variable), the plan diagram looks like this:

    Update plan, highlighting a clustered index update operation
    Update plan, highlighting a clustered index update operation

    Note the highlighted operator (Clustered Index Update), which also has an extra notification about how many non-clustered indexes are also affected by the operation (specific indexes are listed in the tooltip):

    Tooltip for clustered index update operation, showing affected index names
    Tooltip for clustered index update operation, showing affected index names

    (Note that not all indexes on the table are directly affected by an update to the clustered index, so this is not a simple pull from sys.indexes.)

    This plan also gives us some implicit convert information that would be tough to find otherwise. The tooltip for the initial node gives similar information to what Management Studio provides, about implicit conversions:

    Tooltip on root node, showing an implicit conversion warning
    Tooltip on root node, showing an implicit conversion warning

    We also have an Expressions tab which digs into every single expression used in the query, and allows you to quickly spot all of the implicit convert and other operations that could be an issue:

    Expressions tab, showing simple and expanded versions of all expressions used
    Expressions tab, showing simple and expanded versions of all expressions used

    Correct_Math

     
    This demo uses the files Correct_Math.queryanalysis and Correct_Math.sqlplan.

    The two plans in these files are based on this batch:

    SELECT -- courtesy Paul White 
      COUNT_BIG(DISTINCT [TH].[ReferenceOrderID]),
      COUNT_BIG(DISTINCT [TH].[TransactionDate]),
      COUNT_BIG(DISTINCT [TH].[TransactionType]) 
    FROM [Production].[TransactionHistory] [TH] 
    WHERE [ProductID] = 1;
     
    SELECT 1 WHERE EXISTS 
    (SELECT 1 FROM Production.TransactionHistory AS th 
     WHERE th.ProductID = 1 
     AND th.TransactionDate >= '20070901' 
     AND th.TransactionDate < '20071231')
    OR EXISTS 
    (SELECT 1 FROM Production.TransactionHistory AS th 
    WHERE th.ProductID <> 1
     AND th.TransactionDate >= '20070901' 
     AND th.TransactionDate < '20071231');

    First, we can show how Management Studio's math can be off, on both of these plans:

    Plan in Management Studio, showing percentages add up to more than 100%
    Plan in Management Studio, showing percentages add up to more than 100%

    This problem is prevalent and extremely well documented, though all bugs have been closed as "By Design" or "Won't Fix" – see Connect #267530, Connect #370798, and Connect #621330.

    In Plan Explorer, we get the math right in all of these cases:

    Same plan in Plan Explorer, where percentages are adjusted accurately
    Same plan in Plan Explorer, where percentages are adjusted accurately

    Now, estimated cost percentages are not the most valuable piece of information in any plan, and they shouldn't be used in isolation to make any decisions. But it is certainly better to see the work represented exactly as it is distributed, when that information is available.

    There are also some shady things that go on in the SSMS tooltips, that may make you think plans have a lower cost or touch fewer rows than they actually do. Note the highlighted values in these tooltips for the key lookup in the first plan:

    Comparison of Key Lookup tooltips, SSMS on the left, Plan Explorer on the right
    Comparison of Key Lookup tooltips, SSMS on the left, Plan Explorer on the right

    On the left, Management Studio shows the number of rows per iteration in the estimated plan, but total (number of rows per execution times number of executions) in the actual plan. In Plan Explorer, we try to correct these calculations in several places to reflect what is really going on, in both the estimated plan and the actual plan.

    Paul White (@SQL_Kiwi) talks about an even more egregious case in his blog post, "Cardinality Estimation Bug with Lookups."

    Plan Upload Functionality

     
    Plan Explorer also includes a feature that allows you to upload plans to our Q & A site, answers.SQLPerformance.com, where folks like myself, Paul White (@SQL_Kiwi), and Jonathan Kehayias (@SQLPoolBoy), among others, will help you analyze your execution plans and make tuning recommendations. You can start this process by clicking on the Post to SQLPerformance.com button on the toolbar:

    Toolbar button for posting plans to answers.SQLPerformance.com
    Toolbar button for posting plans to answers.SQLPerformance.com

    Rather than repeat all of the information here, I welcome you to review my blog post detailing how this functionality works:

    The only change we've made since that post is the ability to anonymize your plans before uploading, using the Anonymize button on the toolbar:

    Anonymize button on the toolbar, and its descriptive tooltip
    Anonymize button on the toolbar, and its descriptive tooltip

Demos Involving PRO Features

The free version of Plan Explorer can help you solve many vexing execution plan and query tuning problems. But we have added some features to the PRO version that can help even further. For a comprehensive overview, please see these blog posts by Greg Gonzalez (@SQLsensei):

In the meantime, the below examples should help you get a first-hand feel for these features.

    Multiple Tabs

     
    This demo is fairly easy to demonstrate, using any two plans. Just open two files separately using File > Open (if you try this in the free edition, it will just replace the current open plan with the new one you select). Not only can you switch back and forth between plans – without having to open multiple copies of the application – but you can even split off the tabs into different tab groups, just like in Management Studio or Visual Studio. This allows you to compare plans side-by-side:

    Viewing two plans side by side
    Viewing two plans side by side

    Or stacked vertically:

    multi_tab_bottom
    Viewing two plans stacked vertically

    History and Comments

     
    This demo uses the file Comments_History.pesession.

    Plan Explorer PRO allows you to maintain what's known as a .pesession file, where every plan you generate is assigned a version. You can add comments to every version that is generated, and then cycle between them, making it very easy to track which change made a certain impact on the results. In this case, I wrote a simple query, then made a few changes, generating a plan each time. I added the comments so that I could easily show which changes I made to get certain results from the plan:

    History of query versions and comments
    History of query versions and comments

    By default, Plan Explorer PRO will save a version of the query every time you make tangible changes to the query text, but you can also set it so that it saves changes every time you generate a plan, even if you don't make changes to the query. This can be useful when you are making other underlying changes, such as index modifications, statistics updates, server setting changes, etc. You can control this behavior with a setting in Edit > Preferences called Only save history when command text or connection settings change:

    Preferences option to only save history when query text / connection change
    Preferences option to only save history when query text / connection change

    While users of Plan Explorer FREE cannot make direct use of this feature, you can still open a .pesession file in the free product; it just asks you which version of the session file you want to open:

    Opening a .pesession file in Plan Explorer FREE
    Opening a .pesession file in Plan Explorer FREE

    Wait Stats

     
    This demo uses the file Wait_Stats.queryanalysis.

    As long as you are running against SQL Server 2008 or greater, Plan Explorer PRO will – in addition to all of the other runtime metrics provided – gather the actual waits accumulated by just your session. It does this by creating a custom Extended Events session for the duration of your batch. The waits are presented in a tab called Wait Stats in the top pane; note that the appearance of this tab is context-sensitive, and only appears when you've generated an actual plan that accrued measurable waits.

    For this example, I used the following contrived batch to generate some interesting waits across a variety of categories:

    SELECT TOP (10000) 
      FORMAT(OrderDate, 'yyyy-MM-dd'),
      FORMAT(OrderDate, 'MM/dd/yyyy'),
      TRY_CONVERT(HierarchyId, 0x01),
      TRY_PARSE(SalesOrderNumber AS INT)
    FROM 
    (
      SELECT SalesOrderNumber, OrderDate
        FROM Sales.SalesOrderHeader
    ) AS x 
    ORDER BY NEWID()
    OPTION (QUERYTRACEON 8649);
     
    EXEC master..xp_cmdshell 'dir c:\ /s', NO_OUTPUT;
     
    WAITFOR DELAY '00:00:05';

    This resulted in the following Wait Stats tab:

    Sortable wait stats for this session
    Sortable wait stats for this session

    Full Call Stack

     
    This demo uses the files FullCallStack_free.sqlplan, FullCallStack_free.queryanalysis, FullCallStack_Pro.sqlplan, and FullCallStack_Pro.queryanalysis.

    For this demo, we're going to simulate a series of statements that are getting executed using dynamic SQL:

    DECLARE @id INT, @sql NVARCHAR(MAX);
     
    DECLARE c CURSOR LOCAL FAST_FORWARD
    FOR
      SELECT TOP (10) SalesOrderID 
        FROM Sales.SalesOrderHeader;
     
    OPEN c;
     
    FETCH c INTO @id;
     
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
      SET @sql = N'SELECT * FROM Sales.SalesOrderDetail
        WHERE ' + CASE 
    	  WHEN @id = 43702 THEN N'1=1' ELSE 'SalesOrderID = @id' 
    	END + ';';
     
      EXEC sp_executesql @sql, N'@id INT', @id;
     
      FETCH c INTO @id;
    END
     
    CLOSE c;
    DEALLOCATE c;

    I've kept this pretty simple, but imagine that the makeup of the dynamic SQL statement is dependent on a lot of logic only found in the query. When you look at the results for these statements in Management Studio or Plan Explorer FREE, you only see the plans and statements associated with what SQL Server ultimately executed:

    Plan Explorer FREE shows only the resulting statements sent to SQL Server
    Plan Explorer FREE shows only the resulting statements sent to SQL Server

    This doesn't give you any insight at all into why that one instance of the query had most of the estimated cost, high duration and CPU, reads equivalent to scanning the entire table, and a very large number of rows. However, if you execute the same batch by generating an actual plan using Plan Explorer PRO, you get much better information from the full call stack:

    Plan Explorer PRO shows all of the nested statements that led to the eventual queries
    Plan Explorer PRO shows all of the nested statements that led to the eventual queries

    Now, again, this case was quite trivial, but you can see that you have full access to a lot more information about how that particular query came about (if you expand the statement column, you would see the full CASE expression). This can be useful in scenarios where you have stored procedures calling stored procedures calling stored procedures, or dynamic SQL generating dynamic SQL in a dynamic SQL loop inside a cursor, and several other scenarios I'm sure you have come across. In Management Studio or Plan Explorer, all you'd ever see are the final results.

    Flexible Layout

     
    Greg Gonzalez (@SQLsensei) has done a thorough job explaining our plan layout functionality in his blog post, "Plan Explorer PRO 2.5: Query plans your way." The best way to learn about how this functionality can work best for you is to just play with it a little on any plan you have open in Plan Explorer PRO – there are a lot of options (I've squished this a bit to make it fit here):

    Plan layout options in Plan Explorer PRO
    Plan layout options in Plan Explorer PRO

    To demonstrate what these things do, I'll use one of the plans from above (but you could use any complex enough plan):

    The plan we'll use to demonstrate layout features
    The plan we'll use to demonstrate layout features

    Zoom does exactly what you might expect, but using an easy slider instead of context menu items. Filter allows you to hide the branches of a plan that fall below the specified threshold – this can be really useful on extremely large plans. If you want to do this manually, you can simply use the arrow next to any operator at the head of a subtree:

    How to manually expand / collapse any subtree
    How to manually expand / collapse any subtree

    Stretch allows you to shrink or expand the plan by eliminating white space horizontally, while Flatten does the same thing vertically. Mode offers some interesting, alternative layouts, such as Centered, which presents this plan as follows:

    Changing the mode to Centered
    Changing the mode to Centered

    Link Style changes the connectors between operators between the traditional 90° angle and the angled connectors (which you've seen on almost all plan diagrams above). The Rotate buttons do exactly what's on the tin; if you prefer reading a plan right-to-left, you can do that by clicking either button twice, which will turn the above centered plan into this:

    Flipping the plan around to read it left-to-right
    Flipping the plan around to read it left-to-right

    Auto-Fit attempts to resize the plan automatically, so you don't have to do it manually after you've added filters or changed the layout. And finally, the Defaults button resets the view to the application defaults.

    Note that if you use Plan Explorer FREE to open a plan with custom layouts applied, you'll be able to toggle between using and ignoring those settings (but you won't be able make any custom layout changes):

    Plan Explorer FREE allows you to see the layout choices made in PRO
    Plan Explorer FREE allows you to see the layout choices made in PRO

    Deadlock Analysis

     
    This demo uses the file AWDeadlock.xdl.

    Analyzing and resolving deadlocks in SQL Server is no picnic. One reason is because of how hard it can be to collect enough information about the deadlock to really understand why it is caused. Another reason is that even when you're collecting all of the information that you can, the data is not necessarily presented in the most intuitive way.

    As an example, here is how Management Studio displays a simple reverse-order deadlock:

    Limited information available in a traditional deadlock graph
    Limited information available in a traditional deadlock graph

    Plan Explorer PRO allows you to open and investigate this exact same file, and shows the information in a much more useful way than Management Studio. We provide information about all of the processes involved with the deadlock, including statement text, all object and other resource names in plain text, and our diagram even shows the order in which locks are taken:

    We can extract and present much more useful information from the XML
    We can extract and present much more useful information from the XML

    It doesn't matter if you captured the .xdl from a trace, using trace flags 1204 / 1222, or from monitoring tools (for example, SQL Sentry captures deadlocks automatically for you – more details here). Plan Explorer can be a great alternative to giving everyone full access to the SQL Sentry client just so that they can investigate the occasional deadlock.

Assistance for Presenters

Interested in presenting about Plan Explorer to a User Group or SQL community event? We'll be happy to help out with a free, perpetual license for Plan Explorer PRO!

Up front, we'll simply want to know where and when you are presenting (including a link to the event).

After the event, we'll ask for a few simple things:

  • A copy of the deck and samples you presented
  • Rough number of people who attended
  • Any feedback about the tool, or questions you couldn't answer
  • How this demo kit worked for you

That’s it! If you'd like more information about this program, please e-mail us at community@sentryone.com.

Accolades

Plan Explorer wouldn't exist, never mind be as useful as it is, without a lot of work from a lot of people. Without trying to sound like we're tooting our own horns (or those of a few of our good friends), I want to encourage you to thank these people for the countless hours they have put into this resource in various ways:

  • Greg Gonzalez (blog | @SQLsensei) and Brooke Philpott (blog | @MacroMullet) for the ideas and implementation;
  • Steve Wright (blog | @SQL_Steve) and his team for all of the QA and testing;
  • Paul White (blog | @SQL_Kiwi) for discovering a lot of fun and interesting bugs – a few in our tool, and a few more in SQL Server;
  • Jonathan Kehayias (blog | @SQLPoolBoy) for bringing us wacky plans and deadlocks that literally break our tool and, in turn, make it better;
  • the #SQLServer community at large, for coming up with many suggestions that helped shape the product; and,
  • the entire SentryOne team (@SentryOne) for, well, everything.