SET SHOWPLAN_XML (Transact-SQL)

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (solo grupo de SQL dedicado)

Hace que SQL Server no ejecute instrucciones de Transact-SQL. En su lugar, SQL Server devuelve información detallada de cómo se van a ejecutar las instrucciones con el formato de un documento XML bien definido.

Convenciones de sintaxis de Transact-SQL

Sintaxis

SET SHOWPLAN_XML { ON | OFF }

Nota:

Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.

Comentarios

La opción SET SHOWPLAN_XML se establece en tiempo de ejecución, no en tiempo de análisis.

Cuando la opción SET SHOWPLAN_XML está activada, SQL Server devuelve información sobre el plan de ejecución de cada instrucción sin ejecutarla y no se ejecutan las instrucciones Transact-SQL. Cuando esta opción está activada, se devuelve información acerca del plan de ejecución de todas las instrucciones de Transact-SQL siguientes hasta que se vuelve a desactivar. Por ejemplo, si se ejecuta una instrucción CREATE TABLE cuando la opción SET SHOWPLAN_XML está activada y después se ejecuta una instrucción SELECT en la que se especifica la tabla creada, SQL Server devuelve un mensaje de error, ya que la tabla no existe. Por ello, las referencias posteriores que se hagan a la tabla generarán un error. Cuando SET SHOWPLAN_XML está deactivada, SQL Server ejecuta las instrucciones sin generar ningún informe.

SET SHOWPLAN_XML está diseñada para devolver datos de tipo nvarchar(max) para aplicaciones como la utilidad sqlcmd, donde otras herramientas usan después la salida XML para mostrar y procesar la información del plan de consulta.

Nota

La vista de administración dinámica, sys.dm_exec_query_plan, devuelve la misma información que SET SHOWPLAN XML con el tipo de datos xml. Esta información se devuelve desde la columna query_plan de sys.dm_exec_query_plan. Para más información, consulte sys.dm_exec_query_plan (Transact-SQL).

SET SHOWPLAN_XML no se puede especificar en un procedimiento almacenado. Debe ser la única instrucción en un lote.

SET SHOWPLAN_XML devuelve información como un conjunto de documentos XML. Cada lote después de la instrucción SET SHOWPLAN_XML ON se refleja en la salida con un único documento. Cada documento contiene el texto de las instrucciones del lote, seguido de los detalles de los pasos de ejecución. El documento muestra los costos estimados, el número de filas, los índices a los que se ha obtenido acceso y los tipos de operadores utilizados, el orden de combinación y más información acerca de los planes de ejecución.

Nota

Si se selecciona Incluir plan de ejecución real en SQL Server Management Studio, esta opción SET no general la salida del plan de presentación XML. Desactive la opción Incluir plan de ejecución real antes de usar la opción SET.

Los planes de ejecución estimados mediante SSMS y SET SHOWPLAN_XML están disponibles para grupos de SQL dedicados (anteriormente SQL DW) y grupos de SQL dedicados de Azure Synapse Analytics. Para recuperar un plan de ejecución real para grupos de SQL dedicados (anteriormente SQL DW) y grupos de SQL dedicados de Azure Synapse Analytics, hay diferentes comandos. Para más información, consulte Supervisión de la carga de trabajo del grupo de SQL dedicado de Azure Synapse Analytics mediante DMV.

Ubicación de la salida de SHOWPLAN

El documento que contiene el esquema XML de la salida XML de SET SHOWPLAN_XML se copia durante la instalación en un directorio local del equipo en el que se instala Microsoft SQL Server. Encontrará el documento en la unidad que contiene los archivos de instalación de SQL Server, en una ruta de acceso parecida a la siguiente:

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

En la ruta de acceso anterior, SQL Server 2016 usa el nodo 130\. El número 130 se obtiene del primer nodo del valor devuelto por SELECT @@VERSION, que es 13. En SQL Server 2017, se usaría 140\ en la ruta de acceso, ya que el primer nodo de su valor @@VERSION es 14. En SQL Server 2019, el primer valor de @@VERSION es 15. En SQL Server 2022, el primer valor de @@VERSION es 16.

El esquema del plan de presentación también se puede encontrar en Esquemas XML de Microsoft SQL Server.

Permisos

Para utilizar SET SHOWPLAN_XML, debe disponer de permisos suficientes para ejecutar las instrucciones en las que se ejecuta SET SHOWPLAN_XML, y debe tener el permiso SHOWPLAN para todas las bases de datos que contengan objetos a los que se hace referencia.

Para producir un plan de presentación con las instrucciones SELECT, INSERT, UPDATE, DELETE, EXEC *stored_procedure* y EXEC *user_defined_function*, el usuario debe:

  • Tener los permisos correspondientes para ejecutar las instrucciones Transact-SQL.

  • Tener el permiso SHOWPLAN en todas las bases de datos que contengan objetos a los que hacen referencia las instrucciones Transact-SQL, como tablas, vistas, etc.

En el caso de otras instrucciones, como DDL, USE *database_name*, SET, DECLARE, SQL dinámico, etc., solo hacen falta los permisos necesarios para ejecutar las instrucciones de Transact-SQL.

Ejemplos

Las dos instrucciones siguientes utilizan la opción SET SHOWPLAN_XML para mostrar la forma en que SQL Server analiza y optimiza el uso de índices en las consultas.

La primera consulta usa el operador de comparación (=) en la cláusula WHERE de una columna indexada. La segunda consulta utiliza el operador LIKE en la cláusula WHERE. De este modo, SQL Server debe utilizar un recorrido de índice clúster para encontrar los datos que cumplen la condición de la cláusula WHERE. Los valores de los atributos EstimateRows y EstimatedTotalSubtreeCost son más pequeños en la primera consulta indexada, lo que indica que se procesa mucho más rápidamente y que usa menos recursos que la no indexada.

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;

Pasos siguientes