SET SHOWPLAN_XML (Transact-SQL)

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse Analytics（仅限专用 SQL 池）

使 SQL Server 不执行 Transact-SQL 语句。 而 SQL Server 返回有关如何以定义好的 XML 文档格式执行上述语句的详细信息。

Transact-SQL 语法约定

语法

SET SHOWPLAN_XML { ON | OFF }

注意

若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法，请参阅早期版本文档。

注解

SET SHOWPLAN_XML 的设置是在执行或运行时设置的，而不是在分析时设置的。

如果 SET SHOWPLAN_XML 为 ON，则 SQL Server 将返回每个语句的执行计划信息但不执行语句，并且不会执行 Transact-SQL 语句。 将该选项设置为 ON 后，将返回有关所有后续 Transact-SQL 语句的执行计划信息，直到将该选项设置为 OFF 为止。 例如，如果在 SET SHOWPLAN_XML 为 ON 时执行 CREATE TABLE 语句，则 SQL Server 将从涉及同一个表的后续 SELECT 语句返回错误信息：指定的表不存在。 因此，对此表的后续引用将失败。 如果 SET SHOWPLAN_XML 为 OFF，则 SQL Server 将执行语句，但不生成报表。

SET SHOWPLAN_XML 可以为应用程序（如 sqlcmd 实用工具）将输出返回为 nvarchar(max)，其中 XML 输出随后将由其他工具用来显示和处理查询计划信息。

注意

动态管理视图 sys.dm_exec_query_plan 将使用 xml 数据类型返回与 SET SHOWPLAN XML 相同的信息。 该信息是从 sys.dm_exec_query_plan 的 query_plan 列返回的。 有关详细信息，请参阅 sys.dm_exec_query_plan (Transact-SQL)。

不能在存储过程内部指定 SET SHOWPLAN_XML。 该语句必须是批处理中的唯一语句。

SET SHOWPLAN_XML 将返回一组 XML 文档信息。 SET SHOWPLAN_XML ON 语句之后的每个批处理都将在单个文档输出中得到反映。 每个文档都包含批处理中语句的文本，后跟执行步骤的详细信息。 该文档可以显示估计的开销、行数、访问的索引数、执行的运算符的类型、联接次序以及有关执行计划的详细信息。

注意

如果在 SQL Server Management Studio 中选择了“包括实际的执行计划”，则该 SET 选项不会生成 XML 显示计划输出。 请在使用该 SET 选项之前清除“包括实际的执行计划”按钮。

通过 SSMS 和 SET SHOWPLAN_XML 估计的执行计划可用于专用 SQL 池（前身为 SQL DW）和 Azure Synapse Analytics 中的专用 SQL 池。 若要检索专用 SQL 池（前身为 SQL DW）和 Azure Synapse Analytics 中的专用 SQL 池的实际执行计划，可以使用不同的命令。 有关详细信息，请参阅使用 DMV 监视 Azure Synapse Analytics 专用 SQL 池工作负载。

显示计划输出的位置

在安装过程中，将把包含 XML 架构（该架构用于 SET SHOWPLAN_XML 生成的 XML 输出）的文档复制到安装了 Microsoft SQL Server 的计算机的本地目录中。 可在包含 SQL Server 安装文件的驱动器上找到该文档，路径如下所示：

  • \Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

在前面的路径中，SQL Server 2016 使用的是节点 130\。 数字 130 派生自由 SELECT @@VERSION 返回的值的第一个节点，即 13。 SQL Server 2017 的路径将使用 140\，这是因为它的 @@VERSION 值的第一个节点是 14。 对于 SQL Server 2019，@@VERSION 的第一个值是 15。 对于 SQL Server 2022，@@VERSION 的第一个值是 16。

显示计划架构也可以在 Microsoft SQL Server XML 架构中找到。

权限

必须对执行 SET SHOWPLAN_XML 的语句具有足够的执行权限，必须对包含被引用对象的所有数据库具有 SHOWPLAN 权限。

对于 SELECT、INSERT、UPDATE、DELETE、EXEC *stored_procedure* 和 EXEC *user_defined_function* 语句，若要生成显示计划，用户必须满足以下条件：

  • 具有执行 Transact-SQL 语句的相应权限。

  • 对包含 Transact-SQL 语句所引用的对象（如表、视图等）的所有数据库拥有 SHOWPLAN 权限。

对于所有其他语句，如 DDL、USE *database_name*、SET、DECLARE、动态 SQL 等，只需要具有执行 Transact-SQL 语句的相应权限。

示例

下面两个语句使用了 SET SHOWPLAN_XML 设置，以显示 SQL Server 在查询中分析和优化索引用法的方法。

第一个查询在索引列上的 WHERE 子句中使用等于比较运算符 (=)。 第二个查询在 WHERE 子句中使用 LIKE 运算符。 这将强制 SQL Server 使用聚集索引扫描并查找满足 WHERE 子句条件的数据。 第一个索引查询的 EstimateRows 和 EstimatedTotalSubtreeCost 属性中的值较小，这表示与非索引查询相比，该查询的处理速度快得多且使用更少的资源。

USE AdventureWorks2022;
GO
SET SHOWPLAN_XML ON;
GO
-- First query.
SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
-- Second query.
SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';
GO
SET SHOWPLAN_XML OFF;

后续步骤