sys.dm_exec_query_plan (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de datos SQL en Microsoft Fabric

La sys.dm_exec_query_plan función de gestión dinámica devuelve el Showplan en formato XML para el lote especificado por el handle del plano. Este plan especificado por el identificador del plan puede estar almacenado en caché o ejecutándose.

El esquema XML del plan de presentación se publica y está disponible en este sitio web de Microsoft. También está disponible en el directorio donde está instalado SQL Server.

Convenciones de sintaxis de Transact-SQL

Syntax

sys.dm_exec_query_plan(plan_handle)

Argumentos

plan_handle

Token que identifica de forma exclusiva un plan de ejecución de consultas de un proceso por lotes que se ha ejecutado y cuyo plan reside en la caché del plan, o se está ejecutando actualmente. plan_handle es varbinary(64).

plan_handle se puede obtener de los siguientes objetos de administración dinámica:

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (Transact-SQL)

• sys.dm_exec_trigger_stats (Transact-SQL)

Tabla devuelta

Comentarios

En las condiciones siguientes, no se devuelve ningún resultado del plan de presentación en la query_plan columna de la tabla devuelta para sys.dm_exec_query_plan:

• Si el plan de consulta especificado por usar plan_handle ha sido expulsado de la caché del plano, la query_plan columna de la tabla devuelto es NULL. Por ejemplo, esta condición podría ocurrir si hay un retraso temporal entre el momento en que se capturó el mango del plano y cuando se usó con sys.dm_exec_query_plan

• Algunas instrucciones Transact-SQL no se almacenan en la caché, como, por ejemplo, instrucciones de operaciones masivas o instrucciones que contienen literales de cadenas de más de 8 KB. Los Showplans XML para tales sentencias no pueden recuperarse usándolo sys.dm_exec_query_plan a menos que el lote esté en ejecución actual, ya que no existen en la caché.

• Si un procedimiento Transact-SQL lote o procedimiento almacenado contiene una llamada a una función definida por el usuario o a SQL dinámico, por ejemplo usando EXEC (cadena), el Showplan XML compilado para la función definida por el usuario no se incluye en la tabla devuelto por sys.dm_exec_query_plan para el procedimiento por lotes o almacenado. En su lugar, debes hacer una llamada separada a sys.dm_exec_query_plan para el handle del plan que corresponde a la función definida por el usuario.

Cuando una consulta ad hoc utiliza parametrización simple o forzada, la query_plan columna contendrá solo el texto de la sentencia y no el plan de consulta real. Para devolver el plan de consulta, llama sys.dm_exec_query_plan al handle de plan de la consulta parametrizada preparada. Puedes determinar si la consulta se parametrizó haciendo referencia a la sql columna de la vista sys.syscacheobjects o a la columna de texto de la vista de gestión dinámica sys.dm_exec_sql_text .

Permissions

Para ejecutar sys.dm_exec_query_plan, un usuario debe ser miembro del rol fijo de administrador del sistema o tener el VIEW SERVER STATE permiso en el servidor.

Permisos para SQL Server 2022 y versiones posteriores

Requiere el permiso VIEW SERVER PERFORMANCE STATE en el servidor.

Examples

Los siguientes ejemplos muestran cómo usar la vista de sys.dm_exec_query_plan gestión dinámica.

Para ver los Planes de Exposición XML, ejecuta las siguientes consultas en el Editor de Power Query de SQL Server Management Studio, luego selecciona ShowPlanXML en la query_plan columna de la tabla que muestra sys.dm_exec_query_plan El Plan de Exposición XML en el panel de resumen de Management Studio. Para guardar el Showplan XML en un archivo, haz clic derecho en ShowPlanXML en la query_plan columna, selecciona Guardar resultados como, nombra el archivo en el formato <file name>.sqlplan; por ejemplo, MyXMLShowplan.sqlplan.

A. Recuperar el plan de consulta en la caché de una consulta o proceso por lotes de Transact-SQL de ejecución lenta

Los planes de consulta para varios tipos de lotes de Transact-SQL, como lotes ad hoc, procedimientos almacenados y funciones definidas por el usuario, se almacenan en caché en un área de memoria denominada caché del plan. Cada plan de consulta almacenado en la caché está identificado mediante un identificador exclusivo denominado identificador del plan. Puedes especificar este handle de plan con la sys.dm_exec_query_plan vista de gestión dinámica para recuperar el plan de ejecución de una consulta o lote de Transact-SQL concreto.

Si una consulta o lote Transact-SQL se ejecuta durante mucho tiempo en una conexión determinada con SQL Server, recupere el plan de ejecución de dicha consulta o lote para averiguar la causa de la demora. En los siguientes ejemplos se muestra cómo recuperar el plan de presentación XML de una consulta o proceso por lotes de ejecución lenta.

Primero, recupera el ID de sesión (SPID) del proceso que está ejecutando la consulta o lote utilizando el sp_who procedimiento almacenado:

exec sp_who;  
GO  

El conjunto de resultados que se devuelve indica sp_who que el ID de sesión es 54. Puedes usar el ID de sesión con la sys.dm_exec_requests vista de gestión dinámica para recuperar el handle del plan usando la siguiente consulta:

SELECT * FROM sys.dm_exec_requests  
WHERE session_id = 54;  
GO  

La tabla que se devuelve indica sys.dm_exec_requests que el handle de plan para la consulta o lote de ejecución lenta es 0x06000100A27E7C1FA821B10600, que puedes especificar como plan_handle argumento con sys.dm_exec_query_plan para recuperar el plan de ejecución en formato XML de la siguiente manera. El plan de ejecución en formato XML para la consulta o lote de ejecución lenta está contenido en la query_plan columna de la tabla devuelto por sys.dm_exec_query_plan.

SELECT * 
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);  
GO  

B. Recuperar todos los planes de consulta de la memoria caché del plan

Para recuperar una instantánea de todos los planes de consulta que residen en la caché del plan, recupere los identificadores de todos los planes de consulta de la caché; para ello, consulte la vista de administración dinámica sys.dm_exec_cached_plans. Los identificadores del plan se almacenan en la columna plan_handle de sys.dm_exec_cached_plans. Acto seguido, utilice el operador CROSS APPLY para pasar los identificadores del plan a sys.dm_exec_query_plan como se indica a continuación. La salida del plan de presentación XML de todos los planes almacenados actualmente en la caché del plan se muestra en la columna query_plan de la tabla devuelta.

SELECT * 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO  

C. Recuperar todos los planes de consulta para los que el servidor ha reunido estadísticas de consultas procedentes de la memoria caché del plan

Para recuperar una instantánea de todos los planes de consulta para los que el servidor ha reunido estadísticas que residen actualmente en la caché del plan, recupere los identificadores de estos planes de la caché; para ello, consulte la vista de administración dinámica sys.dm_exec_query_stats. Los identificadores del plan se almacenan en la columna plan_handle de sys.dm_exec_query_stats. Acto seguido, utilice el operador CROSS APPLY para pasar los identificadores del plan a sys.dm_exec_query_plan como se indica a continuación. La salida del plan de presentación XML de todos los planes para los que el servidor ha reunido estadísticas almacenadas actualmente en la caché del plan se muestra en la columna query_plan de la tabla devuelta.

SELECT * 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);  
GO  

D. Obtener información acerca de las cinco mejores consultas por promedio de tiempo de CPU

En el ejemplo siguiente se devuelven los planes y el promedio de tiempo de CPU de las cinco mejores consultas.

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
   plan_handle, query_plan   
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)  
ORDER BY total_worker_time/execution_count DESC;  
GO  

Contenido relacionado

• Vistas y funciones de gestión dinámica del sistema
• sys.dm_exec_cached_plans (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• sys.dm_exec_requests (Transact-SQL)
• sys.sp_who (Transact-SQL)
• Referencia de operador de plan de presentación lógico y físico
• sys.dm_exec_text_query_plan (Transact-SQL)