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.

Search results matching tags 'Query Plans', 'showplan', 'SQL Sentry', 'Plan Explorer', and 'graphical plans'

Comparing multiple plans in SQL Sentry Plan Explorer

Getting graphical ShowPlans back in SQL Server 2008 R2