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.

 

 

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

Background

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
    Link
  2. T-SQL Queries – Split Strings
    Link

SQL CLR

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
{
    [Microsoft.SqlServer.Server.SqlFunction(
       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
        {
            get
            {
                //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;
            else
            {
                lastPos = nextPos + delimiterLen;

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

                lastPos = nextPos + delimiterLen;
                nextPos = stringLen;

                if ((nextPos - lastPos) > 0)
                    return true;
                else
                    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;
        }

        #endregion

        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 http://msdn.microsoft.com/en-us/library/78f4aasd.aspx

   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
   go

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

   /*
     DROP FUNCTION dbo.SplitStrings_CLR
     DROP ASSEMBLY CLRUtilities
   */

   CREATE ASSEMBLY CLRUtilities FROM @filename
   WITH PERMISSION_SET = SAFE;
   GO

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

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

   exec sp_configure 'clr enabled',1
   reconfigure with override
   go

 

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)

 

Output:

ListofFruits

 

Utilize Code

 

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

 

/*
    --Adam Machanic
    http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx
    [master]dbo.SplitStrings_CLR

*/
declare @newline nvarchar(30)

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

select

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

                when (tblUCE.class = 1) then
                        object_schema_name(tblUCE.major_id)
                        + '.' + 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)
             tblSQLText

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

          case

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

          end
         ,  tblUCE.statement_line_number desc

Things worked out.  No errors this time.

 

Source Control

GitHub

Files posted to GitHub @ here

 

Dedicated

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
Link