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.
 

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. I recently joined the team at Wayfair and hope to blog about some of the scale challenges I've already started helping to solve.

18 Responses

  1. bender says:

    Thank you!  Annoying!!

  2. drsql says:

    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!

  3. AaronBertrand says:

    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.

  4. AaronBertrand says:

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

  5. Tarek Omar says:

    Thank you, this is really neat..
    thank you

  6. Henrik Staun Poulsen says:

    Great blog entry.
    Thank you.

  7. sqljunky says:

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

  8. ivanrdgz says:

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

  9. SandraV says:

    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.

  10. AaronBertrand says:

    @Sandra sorry it should be .sqlplan.

  11. Doug says:

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

  12. Andrew Lockwood says:

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

  13. anoopsihag says:

    Thank you! Aaron. It is really helpful.

  14. Jamie Greenhough says:

    Thanks, this fixed me up. Much appreciated Aaron!

  15. Christian Flyvholm Christensen says:

    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

  16. soorya says:

    Thanks you.

  17. Eric says:

    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 !

  18. Lakshmi says:

    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.