sys.dm_exec_query_plan (Transact-SQL)

Se aplica a: síSQL Server (todas las versiones admitidas) SíAzure SQL Database

Devuelve el plan de presentación en formato XML para el lote especificado por el identificador del plan. 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 de instalación de SQL Server.

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQL

Sintaxis

sys.dm_exec_query_plan(plan_handle)  

Argumentos

plan_handle
Es un token que identifica de forma única un plan de ejecución de consultas para un lote que se ha ejecutado y su plan reside en la caché del plan o que se está ejecutando actualmente. plan_handle es varbinary(64).

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

Tabla devuelta

Nombre de la columna Tipo de datos Descripción
Dbid smallint Identificador de la base de datos de contexto que estaba activa al compilarse la instrucción Transact-SQL correspondiente a este plan. En el caso de instrucciones SQL ad hoc y preparadas, identificador de la base de datos en que se compilaron las instrucciones.

Esta columna acepta valores NULL.
objectid int Identificador del objeto (por ejemplo, procedimiento almacenado o función definida por el usuario) de este plan de consulta. Para lotes "ad hoc" y preparados, esta columna es null.

Esta columna acepta valores NULL.
número smallint Entero de procedimiento almacenado numerado. Por ejemplo, un grupo de procedimientos de la aplicación orders puede llamarse orderproc;1, orderproc;2, etc. Para lotes "ad hoc" y preparados, esta columna es null.

Esta columna acepta valores NULL.
Encriptados bit Indica si el procedimiento almacenado correspondiente está cifrado.

0 = no cifrado

1 = cifrado

La columna no acepta valores NULL.
query_plan xml Contiene la representación del plan de presentación en tiempo de compilación del plan de ejecución de consultas que se especifica con plan_handle. El plan de presentación está en formato XML. Se genera un plan para cada lote que contiene, por ejemplo, instrucciones Transact-SQL "ad hoc", llamadas a procedimientos almacenados y llamadas a funciones definidas por el usuario.

Esta columna acepta valores NULL.

Observaciones

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

  • Si el plan de consulta que se especifica mediante plan_handle se ha expulsado de la caché del plan, la columna query_plan de la tabla devuelta es NULL. Por ejemplo, esta condición puede producirse si hay un retraso entre el momento en que se captura el identificador del plan y el momento en que se utiliza 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 planes de presentación XML de dichas instrucciones no se pueden recuperar mediante sys.dm_exec_query_plan a menos que el lote esté ejecutándose en ese momento porque no existen en la caché.

  • Transact-SQL Si un lote o procedimiento almacenado contiene una llamada a una función definida por el usuario o una llamada a SQL dinámico, por ejemplo mediante EXEC (cadena), el plan de presentación XML compilado para la función definida por el usuario no se incluye en la tabla devuelta por sys.dm_exec_query_plan para el lote o el procedimiento almacenado. En su lugar, debe realizar una llamada independiente a sys.dm_exec_query_plan para el identificador de plan que corresponde a la función definida por el usuario.

Cuando una consulta "ad hoc" usa parametrización simple o forzada, la columna query_plan contendrá únicamente el texto de la instrucción y no el plan de consulta real. Para devolver el plan de consulta, llame a sys.dm_exec_query_plan con el identificador del plan de la consulta con parámetros preparada. Puede determinar si la consulta era con parámetros haciendo referencia a la columna sql de la vista sys.syscacheobjects o a la columna de texto de la vista de administración dinámica sys.dm_exec_sql_text.

Nota

Debido a una limitación en el número de niveles anidados permitidos en el tipo de datos xml, sys.dm_exec_query_plan no puede devolver planes de consulta que cumplan o superen los 128 niveles de elementos anidados. En las versiones anteriores de SQL Server, esta condición impedía la devolución del plan de consulta y generaba el error 6335. En SQL Server 2005 (9.x) Service Pack 2 y versiones posteriores, la columna query_plan devuelve NULL.
Puede usar la función sys.dm_exec_text_query_plan (de administración dinámica de Transact-SQL) para devolver la salida del plan de consulta en formato de texto.

Permisos

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

Ejemplos

En los siguientes ejemplos se muestra cómo usar la vista de administración dinámica sys.dm_exec_query_plan.

Para ver los planes de presentación XML, ejecute las siguientes consultas en el Editor de consultas de SQL Server Management Studio y, a continuación, haga clic en ShowPlanXML en la columna query_plan de la tabla devuelta por sys.dm_exec_query_plan. El plan de presentación XML se muestra en el panel de resumen de Management Studio. Para guardar el plan de presentación XML en un archivo, haga clic con el botón derecho en ShowPlanXML en la columna query_plan, haga clic en Guardar resultados como,<file_name> asigne al archivo el nombre .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 de varios tipos de lotes Transact-SQL, como los lotes "ad hoc", procedimientos almacenados y funciones definidas por el usuario, se almacenan en un área de la memoria caché denominada caché del plan. Cada plan de consulta almacenado en la caché está identificado mediante un identificador exclusivo denominado identificador del plan. Puede especificar este identificador del plan mediante la vista de administración dinámica sys.dm_exec_query_plan para recuperar el plan de ejecución de una consulta o lote Transact-SQL determinado.

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.

Nota

Para ejecutar este ejemplo, reemplace los valores de session_id y plan_handle por valores específicos del servidor.

Primero, recupere el SPID (Id. de proceso del servidor) para el proceso que está ejecutando la consulta o proceso por lotes mediante el uso del procedimiento almacenado sp_who:

USE master;  
GO  
exec sp_who;  
GO  

El conjunto de resultados que devuelve sp_who indica que el SPID es 54. Puede utilizar el SPID con la vista de administración dinámica sys.dm_exec_requests para recuperar el identificador del plan mediante la siguiente consulta:

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

La tabla devuelta por sys.dm_exec_requests indica que el identificador de plan para la consulta o lote de ejecución lenta es , que puede especificar como argumento plan_handle con para recuperar el plan 0x06000100A27E7C1FA821B10600de ejecución en formato XML como se indica a sys.dm_exec_query_plan continuación. El plan de ejecución en formato XML de la consulta o proceso por lotes de ejecución lenta se muestra en la columna query_plan de la tabla devuelta por sys.dm_exec_query_plan.

USE master;  
GO  
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.

USE master;  
GO  
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.

USE master;  
GO  
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  

Consulte también

Funciones y vistas de administración dinámica (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sp_who (Transact-SQL)
Referencia de operadores lógicos y físicos del plan de presentación
sys.dm_exec_text_query_plan (Transact-SQL)