2

This is the error message I get when running the query below:

Msg 6335, Level 16, State 102, Line 43 XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.

SELECT *, cast(plantext as xml)
  FROM [my_database].[dbo].[the_PlanHistory]
  where servername='myServer'
  and procname='MyProcedure'
  order by cachedtime desc

I know the problem is related to the cast to xml, how can I fix it, or work around it?

0

2 Answers 2

4

This is discussed by Brent Ozar here

You just need to use try_cast(plantext as xml).

SELECT *, try_cast(plantext as xml)
  FROM [my_database].[dbo].[the_PlanHistory]
  where servername='myServer'
  and procname='MyProcedure'
  order by cachedtime desc

If this is NULL then you can try and copy the plain plantext string from the grid and save it into a file with a .sqlplan extension. You may hit the limit on number of characters the grid cell will allow without truncation for strings too though.

If you do hit that limit or find it otherwise inconvenient you could also add this code after the initial SELECT

DECLARE @id INT,
        @plantext NVARCHAR(MAX);

DECLARE @C1 AS CURSOR;
    
SET @C1 = CURSOR FAST_FORWARD
    FOR SELECT id,
               plantext
  FROM [my_database].[dbo].[the_PlanHistory]
  where servername='myServer'
  and procname='MyProcedure'
               AND try_cast(plantext AS XML) IS NULL;
    
    OPEN @C1;
    
    FETCH NEXT FROM @C1 INTO @id, @plantext;
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
          SELECT @id AS [id] /*So you know what the next plan corresponds to */
    
          SELECT @plantext AS [XML_F52E2B61-18A1-11d1-B105-00805F49916B]
    
          FETCH NEXT FROM @C1 INTO @id, @plantext;
      END 

This will bring back all the problem plans individually. The column alias is one that SSMS treats as XML even though in SQL it is a string. But this only works when the result set is a single row/column.

I have validated that this approach works with the problem "SevenJoins" query in Brent's link and the execution plan displays fine on clicking the XML.

Why XML_F52E2B61-18A1-11d1-B105-00805F49916B?

This is special cased in SSMS because some usages of FOR XML queries do rely on it. The name itself originates from a GUID associated with the IXMLDocument Interface.

I do prefer the Microsoft SQL Server 2005 XML Showplan name though. It is less cryptic, semantically more accurate, and SSMS can special case this and potentially treat it as showplan.

If the actual execution plan option is enabled in SSMS then there is a prospect that the plans with the Microsoft SQL Server 2005 XML Showplan will just be shown automatically in the execution plan tab rather than in a grid that needs to be clicked.

For myself I found doing a SELECT @plantext AS [Microsoft SQL Server 2005 XML Showplan] appeared to work more reliably for this purpose than the direct FETCH in Paul's answer though. When FETCH-ed directly the data reader has a second hidden column "ROWSTAT" that breaks the check that SSMS does for whether it should be displayed in the plan tab.

If doing this almost certainly you would want to start the script with SET STATISTICS XML OFF; to disable the actual execution plans from the current script being intermingled with the ones retrieved from elsewhere.

0
3

An alternative column name fooling SSMS into thinking the string result is showplan:

Brent's example

DBCC FREEPROCCACHE;
GO
SELECT COUNT(*) AS SixJoins FROM master.sys.databases p1
INNER JOIN master.sys.databases p2 ON p2.service_broker_guid = p1.service_broker_guid  AND p2.create_date <> p1.create_date 
INNER JOIN master.sys.databases p3 ON p3.service_broker_guid = p2.service_broker_guid  AND p3.create_date <> p2.create_date 
INNER JOIN master.sys.databases p4 ON p4.service_broker_guid = p3.service_broker_guid  AND p4.create_date <> p3.create_date 
INNER JOIN master.sys.databases p5 ON p5.service_broker_guid = p4.service_broker_guid  AND p5.create_date <> p4.create_date 
INNER JOIN master.sys.databases p6 ON p6.service_broker_guid = p5.service_broker_guid  AND p6.create_date <> p5.create_date 
WHERE p5.database_id > 100
GO
SELECT COUNT(*) AS SevenJoins FROM master.sys.databases p1
INNER JOIN master.sys.databases p2 ON p2.service_broker_guid = p1.service_broker_guid  AND p2.create_date <> p1.create_date 
INNER JOIN master.sys.databases p3 ON p3.service_broker_guid = p2.service_broker_guid  AND p3.create_date <> p2.create_date 
INNER JOIN master.sys.databases p4 ON p4.service_broker_guid = p3.service_broker_guid  AND p4.create_date <> p3.create_date 
INNER JOIN master.sys.databases p5 ON p5.service_broker_guid = p4.service_broker_guid  AND p5.create_date <> p4.create_date 
INNER JOIN master.sys.databases p6 ON p6.service_broker_guid = p5.service_broker_guid  AND p6.create_date <> p5.create_date 
INNER JOIN master.sys.databases p7 ON p7.service_broker_guid = p6.service_broker_guid  AND p7.create_date <> p6.create_date 
WHERE p5.database_id > 100

Solution

DECLARE 
    @c CURSOR,
    @Rows integer;

SET @c = CURSOR FORWARD_ONLY STATIC READ_ONLY 
FOR
    SELECT 
        [Microsoft SQL Server 2005 XML Showplan] = TQP.query_plan
    FROM sys.dm_exec_cached_plans AS CP
    CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
    CROSS APPLY sys.dm_exec_text_query_plan (CP.plan_handle, 0, -1) AS TQP
    WHERE
        ST.[text] LIKE N'SELECT COUNT(*)%'
    ORDER BY 
        CP.size_in_bytes DESC;

OPEN @c;

SET @Rows = @@CURSOR_ROWS;

WHILE @Rows > 0
BEGIN
    FETCH @c;
    SET @Rows -= 1;
END;

Alternatively, if you don't like relying on undocumented special column names:

DECLARE 
    @c CURSOR,
    @Plan nvarchar(max),
    @Rows integer;

SET @c = CURSOR FORWARD_ONLY STATIC READ_ONLY 
FOR
    SELECT 
        TQP.query_plan
    FROM sys.dm_exec_cached_plans AS CP
    CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
    CROSS APPLY sys.dm_exec_text_query_plan (CP.plan_handle, 0, -1) AS TQP
    WHERE
        ST.[text] LIKE N'SELECT COUNT(*)%'
    ORDER BY 
        CP.size_in_bytes DESC;

OPEN @c;

SET @Rows = @@CURSOR_ROWS;

WHILE @Rows > 0
BEGIN
    FETCH NEXT FROM @c INTO @Plan;

    SELECT 
        Tag = 1, 
        Parent = NULL, 
        [ShowPlanXML!1!!xmltext] = @Plan 
    FOR XML EXPLICIT;

    SET @Rows -= 1;
END;

Output

clickable plans

Each plan is clickable to show the full graphical plan.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.