XML 数据类型实例具有太多级别的嵌套节点。最大允许深度为 128 层

Brent Ozar 在此讨论了这一点

你只需要使用try_cast(plantext as xml).

SELECT *,
       try_cast(plantext AS XML) AS planxml
FROM   [my_Safer_database].[dbo].[PS_PlanHistory]
WHERE  servername = 'IR1SH_server_SQL0014P0'
       AND procname = 'ManageInPlayEvents'
ORDER  BY cachedtime DESC 

如果是这样，NULL那么您可以尝试plantext从网格复制纯字符串并将其保存到扩展名为 .sqlplan 的文件中。不过，您可能会达到网格单元允许的字符数限制，而不会截断字符串。

如果您确实达到了该限制或发现它有其他不方便，您也可以在初始之后添加此代码SELECT

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

DECLARE @C1 AS CURSOR;

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT id,
           plantext
    FROM   [my_Safer_database].[dbo].[PS_PlanHistory]
    WHERE  servername = 'IR1SH_server_SQL0014P0'
           AND procname = 'ManageInPlayEvents'
           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 

这将单独带回所有问题计划。列别名被 SSMS 视为 XML，即使在 SQL 中它是一个字符串。但这仅在结果集是单行/列时才有效。

我已验证此方法适用于 Brent 链接中的问题“SevenJoins”查询，并且执行计划在单击 XML 时显示良好。

为什么XML_F52E2B61-18A1-11d1-B105-00805F49916B？

这是 SSMS 中的特殊情况，因为某些FOR XML查询用法确实依赖于它。该名称本身源自与 IXMLDocument Interface 关联的 GUID。

不过我确实更喜欢这个Microsoft SQL Server 2005 XML Showplan名字。它不那么神秘，语义上更准确，SSMS 可以对此进行特殊处理，并可能将其视为展示计划。

如果在 SSMS 中启用了实际执行计划选项，则带有 的计划Microsoft SQL Server 2005 XML Showplan可能会自动显示在执行计划选项卡中，而不是需要单击的网格中。

就我自己而言，我发现这样做似乎比保罗回答中的SELECT ... AS [Microsoft SQL Server 2005 XML Showplan]直接方法更可靠地达到此目的。FETCH当FETCH直接 -ed 时，数据读取器有第二个隐藏列“ROWSTAT”，它破坏了 SSMS 是否应在计划选项卡中显示它的检查。

如果这样做几乎肯定您会希望启动脚本以SET STATISTICS XML OFF;禁用当前脚本中与从其他地方检索到的脚本混合在一起的实际执行计划。

另一种列名会欺骗 SSMS 使其认为字符串结果是 showplan：

布伦特的例子

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

解决方案

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;

输出

每个计划都可以单击以显示完整的图形计划。