Search results matching tags 'Query Plans', 'management studio', and 'SSMS'

Comparing multiple plans in SQL Sentry Plan Explorer

AaronBertrand — Fri, 06 Jan 2012 15:54:00 GMT

I’ve seen the question a few times now: “How do I compare two plans side-by-side in Plan Explorer?” Management Studio allows you to view multiple graphical plans that are visible simultaneously, either by generating plans for every statement in a batch, or by generating plans in separate query windows and then splitting the panes. However, at least in my estimation, a lot of the other metrics and properties are more valuable when performing comparisons. In SSMS this is tough, because you can only have one tooltip visible at a time (regardless of how many plans are visible). Same for the Properties panel – only a single plan or operator can populate this panel at one time. So even though you can compare high-level graphical details quickly, you will likely end up with multiple instances of SSMS to perform true side-by-side comparisons of the lower-level details.

In Plan Explorer (free download) it is very easy to compare a lot of the metrics side-by-side for two (or more) plans. Since Plan Explorer can generate estimated or actual plans for you, it’s as simple as entering multiple queries in the Command Text pane, and then generating an estimated or actual plan:

I recommend trying the Vertical Layout, so that the various plan-related tabs and the Statements Tree are a lot closer together (reducing mouse and eye movement):

Here is a quick comparison of the two default options (vertical on the left, horizontal on the right – click to enlarge):

(There is also a Custom Layout option if you wish to change from the default options.)

And you can still compare a lot of the other details about two different statements by looking at the other columns in the Statements Tree tab. For example, comparing costs, row counts and operation counts between estimated or actual plans can give you a much more accurate picture of the performance impact of different statements:

And you can switch between graphical plans quickly by using arrow keys or mouse clicks to switch between statements:

Since the other tabs are context sensitive, you can do this for any tab: simply highlight a tab (e.g. Top Operations) and, as you switch through the rows in the Statements Tree, the tab below will update accordingly.

For more complex plans, and depending on how much screen real estate you have, you may prefer to open two instances of Plan Explorer so that you can interact with the plans a little more independently. In this case you won’t be able to see percentages or other relative comparisons directly against one another, but for most eyeballing, it’s still better than what you get in Management Studio IMHO.

That all said, comparing two graphical plans side by side is definitely on the list for a future update; in the meantime, I hope the above is helpful.

Getting graphical ShowPlans back in SQL Server 2008 R2

AaronBertrand — Fri, 28 Oct 2011 17:42:00 GMT

For those of you using the SQL Server 2008 R2 version of Management Studio, and have applied Service Pack 1, you have probably noticed that clicking on a ShowPlan in grid results no longer shows the graphical plan, but rather opens a new window with the raw XML.

For example, running the following query:

SELECT TOP (5) qp.query_plan
   FROM sys.dm_exec_query_stats AS qs
   CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp;

...yields the following results:

Clicking on any of those hyperlinks, prior to applying Service Pack 1 (or in earlier versions of SSMS), would launch a graphical plan -- which you could examine inline, save to the file system, or open in SQL Sentry Plan Explorer. After applying Service Pack 1, however, this launches a new XML editor window:

...which isn't very useful, unless you're really good at speed-reading XML. This was reported by @AdamMachanic in Connect #652855 (for some reason, closed as "by design") and has been observed by countless others. The problem is caused because, due to slight changes in SP1, SSMS has difficulty determining that the resulting XML is, in fact, a ShowPlan document.

One workaround is to save the XML as a .sqlplan file, and then launch it by double-clicking or using File > Open in either SSMS or Plan Explorer. And as @Kendra_Little pointed out on twitter, you can also just right-click the result, Copy, and then paste into Plan Explorer.

Another workaround, if you have a ~~Denali~~ SQL Server 2012 installation handy, is to copy the newer showplanxml.xsd file from SQL Server 2012 and use it with SQL Server 2008 R2. To do this, close down all instances of Management Studio, open Windows Explorer on the machine running SQL Server 2012, and go to the following folder:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\schemas\sqlserver\2004\07\showplan\

Copy the showplanxml.xsd file. On the machine that is running the 2008 R2 client tools, go to the following folder:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\

Rename the file showplanxml.xsd to showplanxml.xsd.old, and then paste the newer version of the XSD file in its place. (Note that your Program Files folder may be in a slightly different location, and on 32-bit systems, the (x86) won't be there.)

After you have the newer version of the XSD file in the showplan folder, when you open Management Studio and repeat the above steps, you should see the graphical plan as before. This works because it corrects the issue with the invalid XSD file and, even though there have been substantial changes to the XSD schema for SQL Server 2012, the plans you're retrieving from older versions are not going to produce anything that interferes or conflicts with the older XSD, so the new stuff is just fluff.

Now, I haven't done exhaustive research on this (I've tested a few dozen plans with no issues), and it's certainly not supported, but if you want to get the old behavior back without waiting for some future cumulative update, you now have a workaround that doesn't require multiple extra steps for every plan you open. Still, I'd like to hear if you encounter any issues with this workaround.

If you want an official, supported fix from Microsoft, they will be happy to provide you with an updated 2008 R2 version of the XSD file. Before it is released through a cumulative update, however, you'll have to go through official CSS channels to get it. Make sure to reference Connect #652855.

SQL Sentry Plan Explorer v1.3

AaronBertrand — Tue, 25 Oct 2011 16:43:00 GMT

On Friday, we published a new version of our free query plan analysis tool, SQL Sentry Plan Explorer. As noted in the change list, we've added a couple of important features.

SQL Server 2012 support

With a whole slew of changes to the showplan schema in Denali, we've added operators and columns to reflect these changes. For example, you will notice the new Window Spool operator:

As well as the ColumnStore index scan operator, and details about the new batch execution mode:

And while we don't add all new columns by default, you can also optionally show data for new items such as the FORCESCAN hint (also added in SQL Server 2008 R2 SP1).

Table-level I/O metrics

When you generate an Actual Plan from Plan Explorer, we collect additional runtime metrics you won't get from Management Studio's showplan - duration, CPU, and I/O. While it is easy to show the total reads for a query, it is not always easy or intuitive to decipher which object(s) those reads came from. We've added a Table I/O tab that breaks it down by object, making it very easy to see where the majority of your I/O is coming from:

Note that this and other runtime information is not available with an estimated plan of any kind, or with an actual plan generated by Management Studio - the information simply isn't there. We can only show runtime metrics when you generate an actual plan within Plan Explorer (or if you've captured plans using SQL Sentry Performance Advisor).

We hope you find these enhancements useful and, as always, please let us know if you have any issues or questions.

As a side note, the SSMS add-in hasn't changed.

A demo kit for SQL Sentry Plan Explorer

AaronBertrand — Tue, 13 Sep 2011 16:31:00 GMT

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

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