XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels. Gah!

Yeah, that’s pretty annoying! You’re in a rush to troubleshoot the performance of a stored proc that’s gone rogue, and the tool you are using (something like the SQL statement pictured above) is crashing with this XML error with sys.dm_exec_text_query_plan. You don’t have time to fix the tool properly, so you hack it apart and finally get the query plan text displayed graphically, address the issue and hastily move on to the next burning issue.

This blog post presents an upgraded script to take the pain out of that hacking process.  Let’s face it, when you need to see a query plan, you need to see it quickly, not after half an hour of swearing about  your developers for writing code that compiles to such a complex plan, and Microsoft for, well, just being themselves. So, with no further ado:

ScriptName:  StoredProcCachedPerformanceDissection.sql
Description: This script returns statement level performance measures and query plans (in xml format where possible) for a given stored proc. 

Having run this script, to view a query plan listed as 'Not possible':
   1. Copy/paste and run the SQLGetQueryPlan statement in the current query window
   2. Save results to a file named anything.sqlplan and open via SSMS
   (You may also need to increase SSMS Query menu => Query options => Execution => general => Set TextSize  (SSMS 2012))

--Parameters section:

DECLARE @SchemaDotObject NVARCHAR(255) = 'dbo.uspGetTrades' --proc should be in current database
      , @HideQueryPlans BIT = 0              --Set to 1 for pasting results to Excel, or for faster results to client
		, @ThresholdMaxElapsedSeconds INT = 60 --Filter out statements running quicker than this for results to client

DECLARE @PosDot INT = charindex('.',@SchemaDotObject )
DECLARE @schemaName NVARCHAR(128) = LEFT (@SchemaDotObject ,@PosDot -1)
      , @objectName NVARCHAR(128) = RIGHT(@SchemaDotObject ,LEN(@SchemaDotObject) -@PosDot )

        @objectID INT

SELECT @objectID = p.object_id,
       @schemaName = s.name
FROM   sys.objects p
       JOIN sys.schemas s on p.schema_id = s.schema_id
WHERE  p.name = @objectName
       AND s.name = @schemaName
IF @objectid IS NULL BEGIN
       DECLARE @ErrorMessage VARCHAR(255) = 'stored proc ''' + @SchemaDotObject + ''' not found in current database' 
       SELECT @ErrorMessage 'Error'

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
SELECT Statement = ROW_NUMBER() OVER (ORDER BY QS.statement_start_offset),
		 DatabaseName = DB_NAME(ST.dbid),
       ObjectName = @SchemaDotObject,
       ObjectType = CP.objtype,
       SQLStatement = SUBSTRING(ST.text, QS.statement_start_offset / 2 + 1, 
                              ((CASE WHEN QS.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), ST.text)) * 2) 
                                     ELSE QS.statement_end_offset END) - QS.statement_start_offset) / 2 + 1),
	    NVarcharQP = SQP.query_plan ,
       XMLQP = CONVERT(XML , 'Not possible'),            --update this later with real query plan
       SQLGetQueryPlan = CONVERT(NVARCHAR(255), N'N/A'), --update this later with real sql
       MemoryAddress = CP.memory_object_address,
       PlanCreationTime = QS.creation_time,
       PlanLastExecutionTime = QS.last_execution_time,
       ExecutionCount = QS.execution_count,
       LastElapsedSecs = QS.last_elapsed_time / 1000000,
       LastCPUSecs = QS.last_worker_time / 1000000,
       AverageElapsedSecs = CASE WHEN QS.execution_count = 0 THEN NULL
                                 ELSE QS.total_elapsed_time / QS.execution_count / 1000000
       AverageCPUSecs = CASE WHEN QS.execution_count = 0 THEN NULL
                             ELSE QS.total_worker_time / QS.execution_count / 1000000
       MinElapsedSecs = QS.min_elapsed_time / 1000000,
       MinCPUSecs = QS.min_worker_time / 1000000,
       MaxElapsedSecs = QS.max_elapsed_time / 1000000,
       MaxCPUsecs = QS.max_worker_time / 1000000,
       LastRowCount = QS.last_rows,
       AverageRowCount = CASE WHEN QS.execution_count = 0 THEN NULL
                              ELSE QS.total_rows / QS.execution_count
       MinRowCount = QS.min_rows,
       MaxRowCount = QS.max_rows,
       LastPhysicalReadsMB = QS.last_physical_reads / 128,
       AveragePhysicalReadsMB = CASE WHEN QS.execution_count = 0 THEN NULL
                                     ELSE QS.total_physical_reads / QS.execution_count / 128
       MinPhysicalReadsMB = QS.min_physical_reads / 128,
       MaxPhysicalReadsMB = QS.max_physical_reads / 128,
       LastLogicalReadsMB = QS.last_logical_reads / 128,
       AverageLogicalReadsMB = CASE WHEN QS.execution_count = 0 THEN NULL
                                    ELSE QS.total_logical_reads / QS.execution_count / 128
       MinLogicalReadsMB = QS.min_logical_reads / 128,
       MaxLogicalReadsMB = QS.max_logical_reads / 128,
       LastLogicalWritesMB = QS.last_logical_writes / 128,
       AverageLogicalWritesMB = CASE WHEN QS.execution_count = 0 THEN NULL
                                     ELSE QS.total_logical_writes / QS.execution_count / 128
       MinLogicalWritesMB = QS.min_logical_writes / 128,
       MaxLogicalWritesMB = QS.max_logical_writes / 128
into #Results
FROM   sys.dm_exec_cached_plans AS CP
       CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
       LEFT JOIN sys.dm_exec_query_stats QS
              ON CP.plan_handle = QS.plan_handle
       OUTER APPLY sys.dm_exec_text_query_plan(CP.plan_handle, QS.statement_start_offset, QS.statement_end_offset) SQP
WHERE  ST.dbid = @dbid
       AND ST.objectid = @objectid
ORDER  BY CP.memory_object_address DESC,
          QS.statement_start_offset ASC

DECLARE @Statement INT
SELECT @Statement=MAX(Statement) from #Results
WHILE @Statement>0 BEGIN
      UPDATE #Results SET XMLQP = CONVERT(XML, NVarcharQP) FROM #Results WHERE Statement=@Statement
      UPDATE #Results Set SQLGetQueryPlan = N'SELECT NVarcharQP FROM #Results WHERE Statement = ' + CONVERT (NVARCHAR,@Statement ) WHERE Statement=@Statement
   SELECT @Statement = @Statement - 1

 DatabaseName, ObjectName, ObjectType, Statement, SQLStatement
, CASE WHEN @hideQueryPlans=1 THEN 'hidden' ELSE XMLQP END 'XML Query Plan'
, SQLGetQueryPlan
, MemoryAddress, PlanCreationTime, PlanLastExecutionTime, ExecutionCount
, LastElapsedSecs, LastCPUSecs, AverageElapsedSecs, AverageCPUSecs, MinElapsedSecs, MinCPUSecs, MaxElapsedSecs, MaxCPUsecs
, LastRowCount, AverageRowCount, MinRowCount, MaxRowCount
, LastPhysicalReadsMB, AveragePhysicalReadsMB, MinPhysicalReadsMB, MaxPhysicalReadsMB
, LastLogicalReadsMB, AverageLogicalReadsMB, MinLogicalReadsMB, MaxLogicalReadsMB
, LastLogicalWritesMB, AverageLogicalWritesMB, MinLogicalWritesMB, MaxLogicalWritesMB
FROM #Results
WHERE MaxElapsedSecs > @ThresholdMaxElapsedSeconds
ORDER By Statement

sample output (ignoring the performance metrics):


You should be able to click and instantly view those query plans in a new window. Where the XML Query Plan is listed as “not possible”, that’s because the nvarchar(max) query plan could not be converted to XML, probably because of the depth of nested nodes. Instead copy the SQLGetQueryPlan cell to the query window and execute this line of code:


Save results to anything.sqlplan, and open this new file in SSMS to view the graphical execution plan. Sweet!

P.s. I didn’t write the whole script from scratch. I found the original via google years ago, but cannot remember or find the source to reference. I just made it tolerant of conversion issues, and simplified the process of viewing plans that cannot be stored in xml.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s