SSMS – Query Plan shows XML ( not graphics )


I have a nice query that is looking at our cached plan for a certain pattern.


SQL Server Management Studio ( v2014 )

Here is where we start out…


And, so I click on the query_plan column and saw what I pasted below…

Query Plan as XML


And, yes I am good with XML, but I was hoping for nice intuitive query plan.



SQL Server Management Studio ( v2017 )

Download and Install SSMS v2017

Please download v2017 from here.

Use it as it is a far better tool.

SQL Server Management Studio ( v2014 )

To continue to use SSMS v2014 please do the following


  1. Launch Windows Explorer
  2. Access the following folder C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\schemas\sqlserver\2004\07\showplan
    • Make a backup of the showplanxml.xsd file
    • Access the v2017 version from C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\schemas\sqlserver\2004\07\showplan
    • Overwrite the showplanxml.xsd file in C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\schemas\sqlserver\2004\07\showplan with C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\schemas\sqlserver\2004\07\showplan




Marketing Version Internal Version Folder File Info
 2017  140  C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\schemas\sqlserver\2004\07\showplan  File Date :- Saturday, ‎August ‎22nd, ‎2017, ‏‎2:51:26 AM

File Size :- 95.1 KB (97,406 bytes)

 2016  130  C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :-  ‎Tuesday, ‎February ‎23, ‎2016, ‏‎9:31:06 AM

File Size :- 85.5 KB (87,650 bytes)

 2014  120  C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :- ‎Friday, ‎June ‎17, ‎2016, ‏‎7:31:38 PM

File Size :- 85.2 KB (87,333 bytes)

 2012  110  C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :- ‎Thursday, ‎February ‎20, ‎2014, ‏‎6:32:11 PM

File Size :- 80.3 KB (82,266 bytes)

 2008  100  C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :- ‎Wednesday, ‎July ‎22, ‎2009, ‏‎1:17:32 AM

File Size :- 70.5 KB (72,272 bytes)

 2005  90
 2000  80



Crediting Aaron Bertrand.

Here is his post

Title :- Getting graphical ShowPlans back in SQL Server 2008 R2
Date Published :- October 28, 2011



The reason for the breakage is that each SQL Server Version expands beyond previous ones.

New functionalities are added.

We have new operators, performance lag causation are exposed.

Query plans reflect these changes.

Unfortunately SSMS bundled with previous engines are not aware of this changing landscape; and thus break.

It is not clear whether the change occurs during metadata discovery or doing the actual analysis.

The clear path is to install newer SSMS as they are released or at least one has SQL Server Instances running the equivalent versions.



Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s