SSMS – Query Plan shows XML ( not graphics )

Background

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

Scenario

SQL Server Management Studio ( v2014 )

Here is where we start out…

Grid

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.

 

Remediation

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

Outline

  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

 

Reference

 

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

 

Credit

Crediting Aaron Bertrand.

Here is his post

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

 

Backdrop

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.