October 28, 2011 | SQL Server

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.
 

18 comments on this post

    • bender - October 28, 2011, 9:07 PM

      Thank you!  Annoying!!

    • drsql - October 28, 2011, 9:54 PM

      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!

    • AaronBertrand - October 28, 2011, 11:03 PM

      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.

    • AaronBertrand - October 29, 2011, 4:26 PM

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

    • Tarek Omar - November 1, 2011, 10:16 AM

      Thank you, this is really neat..
      thank you

    • Henrik Staun Poulsen - November 1, 2011, 3:21 PM

      Great blog entry.
      Thank you.

    • sqljunky - November 29, 2011, 8:04 PM

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

    • ivanrdgz - February 7, 2012, 4:09 AM

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

    • SandraV - February 8, 2012, 9:05 PM

      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.

    • AaronBertrand - February 8, 2012, 9:23 PM

      @Sandra sorry it should be .sqlplan.

    • Doug - July 5, 2012, 8:58 PM

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

    • Andrew Lockwood - July 27, 2012, 6:40 PM

      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! 😉

    • anoopsihag - August 2, 2012, 11:26 AM

      Thank you! Aaron. It is really helpful.

    • Jamie Greenhough - September 27, 2012, 12:55 AM

      Thanks, this fixed me up. Much appreciated Aaron!

    • Christian Flyvholm Christensen - January 8, 2013, 5:36 PM

      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

    • soorya - March 30, 2015, 12:51 PM

      Thanks you.

    • Eric - May 6, 2015, 3:06 PM

      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 !

    • Lakshmi - February 23, 2016, 11:14 PM

      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.

Comments are closed.