THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

Getting graphical ShowPlans back in SQL Server 2008 R2

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.
 

Published Friday, October 28, 2011 2:42 PM by AaronBertrand

Comments

 

bender said:

Thank you!  Annoying!!

October 28, 2011 2:07 PM
 

drsql said:

THIS EXPLAINS IT! When I was working on the DMV book, I was using 2008, but the other people must have been using 2008R2. I kept saying that it worked for me, but they said it just opened up the XML. Thanks!

October 28, 2011 2:54 PM
 

AaronBertrand said:

Note that there is an outside possibility that you can generate a plan that still shows the raw XML instead of a graphical plan, regardless of the version of the XSD. I believe such a plan will have some characteristic that the XSD doesn't yet account for (and I suspect this is the issue with Adam's plan, though I am not seeing anything obvious on initial inspection). If you come across this scenario you should definitely share your plan with Microsoft via CSS.

October 28, 2011 4:03 PM
 

AaronBertrand said:

Adam's plan uses a multi-parameter user-defined aggregate, and the current XSD does not yet seem to support this.

October 29, 2011 9:26 AM
 

Tarek Omar said:

Thank you, this is really neat..

thank you

November 1, 2011 3:16 AM
 

Henrik Staun Poulsen said:

Great blog entry.

Thank you.

November 1, 2011 8:21 AM
 

sqljunky said:

Or you could use SSMS 2008 add-on like SQL Assistant to display the graphical SQL plans or a 3pd party tool like Toad

November 29, 2011 12:04 PM
 

Aaron Bertrand said:

Where is my graphical ShowPlan? Many of you on SQL Server 2008 R2 are experiencing a major annoyance

December 1, 2011 9:37 AM
 

ivanrdgz said:

Thanks Aaron. I was going crazy with this annoying "enhancement". I already voted on that connect.

February 6, 2012 8:09 PM
 

SandraV said:

Hmmm...  The .SHOWPLAN doesn't work for me.  It just opens in IE as XML if I double click or opens in SSMS as XML if I do a file open there.

February 8, 2012 1:05 PM
 

AaronBertrand said:

@Sandra sorry it should be .sqlplan.

February 8, 2012 1:23 PM
 

Doug said:

THANK YOU! The .xsd solution worked perfectly for me.

July 5, 2012 1:58 PM
 

Andrew Lockwood said:

Thanks Aaron, I was stuck jumping between two machines and the non-R2 SSMS box was driving me nuts with the raw XML display.

...Probably a good time to upgrade them all to 2012! ;)

July 27, 2012 11:40 AM
 

anoopsihag said:

Thank you! Aaron. It is really helpful.

August 2, 2012 4:26 AM
 

Jamie Greenhough said:

Thanks, this fixed me up. Much appreciated Aaron!

September 26, 2012 5:55 PM
 

Christian Flyvholm Christensen said:

An easy way to find a showplanxml.xsd that works, is to copy it from here:

http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd

January 8, 2013 9:36 AM
 

soorya said:

Thanks you.

March 30, 2015 5:51 AM
 

Eric said:

Thanks Aaron to describe the cause of the problem,

and Christian Flyvholm submitted measures to get the xsd fix.

I just fix the problem on my 2008R2 SSMS client !

May 6, 2015 8:06 AM
 

Lakshmi said:

Thank you Aaron for the article. I have SQL 2012 Standard edition on my PC and suddenly my SSMS stopped showing graphical query plans. I  copied the showplanxml file from sql 2014 and overwrote mine and now it works as it should!

Thank you.

February 23, 2016 3:14 PM
New Comments to this post are disabled

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement