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.



SQL Server – Identify “uncontained” objects — Using SQLCLR to split strings


This is a quick followup to our last post; that post is titled Microsoft – SQL Server – Identify “uncontained” objects ( Link ).

In the post mentioned something about a concerning error :

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.


Technical Discussions

The problem stayed on my mind and took me back to a good and informed set of technical discussions:

  1. Tally OH! An Improved SQL 8K “CSV Splitter” Function
  2. T-SQL Queries – Split Strings


The discussions mentioned that the best path to splitting strings is to use SQL CLR.  And, so I decided to try out that path.


Adam Machanic

Found out from Aaron Bertrand  that Adam Machanic of the WhoisActiveLink ) fame has a well written SQL CLR function that splits strings, as well.

Downloaded Adam’s code from here.

And, started to use it, but quickly found out that it is missing Row Numbers.  Thankfully Surya Pratap laid out a nice trail on how to add “Row Numbers” to the returned table.


Edited Code

Here is Adam’s original code with Surya’ hints ( added in ).


using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class ResultSet

	public readonly int row;

	public readonly string value;

	public ResultSet(int row, string value)

		this.row = row;

		this.value = value;



public partial class UserDefinedFunctions
       FillRowMethodName = "FillRow_Multi",
       TableDefinition = "item nvarchar(4000)"
    public static IEnumerator SplitString_Multi(
      [SqlFacet(MaxSize = -1)]
      SqlChars Input,
      [SqlFacet(MaxSize = 255)]
      SqlChars Delimiter
        return (
            (Input.IsNull || Delimiter.IsNull) ?
            new SplitStringMulti(new char[0], new char[0]) :
            new SplitStringMulti(Input.Value, Delimiter.Value));

    //public static void FillRow_Multi(object obj, out SqlString item)
	public static void FillRow_Multi(object obj, out SqlInt32 row, out SqlString item)

        //item = new SqlString((string)obj);

		ResultSet rs = (ResultSet)obj;

		row = new SqlInt32(rs.row);

		item = new SqlString(rs.value);


    public class SplitStringMulti : IEnumerator

		private int row;

        public SplitStringMulti(char[] TheString, char[] Delimiter)
            theString = TheString;
            stringLen = TheString.Length;
            delimiter = Delimiter;
            delimiterLen = (byte)(Delimiter.Length);
            isSingleCharDelim = (delimiterLen == 1);

            lastPos = 0;
            nextPos = delimiterLen * -1;

			//initalise row in "Constructor" and during "Reset" set row to 0 by adding
			row = 0;

        #region IEnumerator Members

        public object Current
                //return new string(theString, lastPos, nextPos - lastPos);

				String strToken = null;
				ResultSet objResultSet = null;
				strToken = new string (theString, lastPos, nextPos - lastPos);

				objResultSet = new ResultSet(++row, strToken );

				return (objResultSet);


        public bool MoveNext()
            if (nextPos >= stringLen)
                return false;
                lastPos = nextPos + delimiterLen;

                for (int i = lastPos; i  0)
                            return true;
                            i += (delimiterLen-1);
                            lastPos += delimiterLen;

                lastPos = nextPos + delimiterLen;
                nextPos = stringLen;

                if ((nextPos - lastPos) > 0)
                    return true;
                    return false;

        public void Reset()
            lastPos = 0;
            nextPos = delimiterLen * -1;

			//initialise row in "Constructor" and during "Reset" set row to 0 by adding
			row = 0;


        private int lastPos;
        private int nextPos;

        private readonly char[] theString;
        private readonly char[] delimiter;
        private readonly int stringLen;
        private readonly byte delimiterLen;
        private readonly bool isSingleCharDelim;


And, to compile it



   rem C:\windows\Microsoft.NET\Framework\v4.0.30319\csc /out:CLRUtilities.dll /target:library SplitStringMulti.cs
   C:\windows\Microsoft.NET\Framework\v4.0.30319\csc /out:CLRUtilitiesIdentity.dll /target:library SplitStringMultiIdentity.cs

To use the CLR in SQL Server


   use master

   declare @filename sysname
   set @filename = 'C:\AdamMachnic\CLRUtilitiesIdentity.dll'

     DROP FUNCTION dbo.SplitStrings_CLR

   CREATE ASSEMBLY CLRUtilities FROM @filename

   CREATE FUNCTION dbo.SplitStrings_CLR
      @List NVARCHAR(MAX),
     @Delimiter NVARCHAR(255)
   RETURNS TABLE ( Row int, Item NVARCHAR(4000) )
   EXTERNAL NAME CLRUtilities.UserDefinedFunctions.SplitString_Multi;

   exec sp_configure 'show advanced options',1
   reconfigure with override

   exec sp_configure 'clr enabled',1
   reconfigure with override


Try Things Out


Here is our SQL Code for trying things out.


     declare @listofFruits nvarchar(600)
     declare @listSeparator nvarchar(30)

     set @listofFruits = 'Apple, Orange, Pear, Banana, Mango'
     set @listSeparator = ','

     select *
     from   [master].dbo.SplitStrings_CLR(@listofFruits, @listSeparator)





Utilize Code


Here is the SQL Code where we utilize Adam’s code.


    --Adam Machanic

declare @newline nvarchar(30)

set @newline = char(13) + char(10)


        , tblUCE.class_desc
        , tblUCE.major_id as objectID
        , case

                when (tblUCE.class = 1) then
                        + '.' + object_name(tblUCE.major_id)
                else cast(tblUCE.major_id as sysname)

           end as [entity] 

        , tblUCE.statement_line_number

        , len(tblSM.[definition]) as lengthOFDefinition

        ,  tblSM.[definition]

        , tblSQLText.[Item] as sqlLine

from   sys.dm_db_uncontained_entities tblUCE

          inner join sys.sql_modules tblSM

                on tblUCE.major_id = tblSM.object_id

          inner join sys.objects tblO

               on tblSM.object_id = tblO.object_id

         cross apply [master].dbo.SplitStrings_CLR(tblSM.[definition], @newLine)

--filter out encryped objects
where tblSM.[definition] is not null

--filter out Microsoft shipped\owned objects
and   tblO.is_ms_shipped = 0

--match line numbers
and tblUCE.statement_line_number = tblSQLText.[Row]

order by 


                when (tblUCE.class = 1) then
                        + '.' + object_name(tblUCE.major_id)
                else cast(tblUCE.major_id as sysname)

         ,  tblUCE.statement_line_number desc

Things worked out.  No errors this time.


Source Control


Files posted to GitHub @ here



Dedicated to the public square commiters — Erland Sommarskog, Steve Jones, Aaron Bertrand, Adam Machanic, and Jeff Moden.


Listening To

And, listening to that old song

John Michael Montgomery – Letters from Home