sys.dm_exec_query_plan (Transact-SQL)

Sintaxis

sys.dm_exec_query_plan ( plan_handle )

Argumentos

• plan_handle
  Identifica de forma exclusiva un plan de consultas para un lote que está almacenado en caché o ejecutándose.

  plan_handle es de tipo varbinary(64). plan_handle puede obtenerse a partir de los siguientes objetos de administración dinámica:

  sys.dm_exec_cached_plans

  sys.dm_exec_query_stats

  sys.dm_exec_requests

Tabla devuelta

Notas

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 especificado 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é.

• Si un procedimiento almacenado o lote Transact-SQL contiene una llamada a una función definida por el usuario o una llamada a SQL dinámico, por ejemplo, con EXEC (string), el plan de presentación XML compilado de la función definida por el usuario no se incluye en la tabla devuelta por sys.dm_exec_query_plan correspondiente al procedimiento almacenado o lote. Deberá realizar una llamada independiente a sys.dm_exec_query_plan para el identificador del plan que corresponda 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. Para obtener más información acerca de la parametrización, vea Parametrización simple y Parametrizaciones forzadas.

Debido a la limitación del número de niveles anidados permitidos en el tipo de datos xml, sys.dm_exec_query_plan no puede devolver planes de consulta que tengan 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 el Service Pack 2 de SQL Server 2005 y versiones posteriores, la columna de query_plan devuelve NULL. Puede usar la función de administración dinámica sys.dm_exec_text_query_plan (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 de la función fija de servidor sysadmin o disponer del permiso VIEW SERVER STATE 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 secundario en ShowPlanXML en la columna query_plan, haga clic en Guardar resultados como y asigne un nombre al archivo con el formato <nombreDeArchivo>.sqlplan; por ejemplo, MiPlanDePresentaciónXML.sqlplan.

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

USE master;
GO
exec sp_who;
GO

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

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

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

USE master;
GO
SELECT * FROM sys.dm_exec_cached_plans 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 caché del plan

USE master;
GO
SELECT * FROM sys.dm_exec_query_stats 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

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

Vea también

Referencia

Conceptos

Otros recursos