sys.dm_exec_text_query_plan (Transact-SQL)
Devuelve el plan de presentación en formato de texto para un lote Transact-SQL o para una instrucción concreta dentro del mismo. Este plan de consulta especificado por el identificador del plan puede estar almacenado en caché o ejecutándose. Esta función con valores de tabla es similar a sys.dm_exec_query_plan (Transact-SQL), pero tiene las diferencias siguientes:
El resultado del plan de consulta se devuelve en formato de texto.
El resultado del plan de consulta no está limitado en tamaño.
Se pueden especificar instrucciones individuales dentro del lote.
.gif "Icono de vínculo a temas")
Convenciones de sintaxis de Transact-SQL
Sintaxis
sys.dm_exec_text_query_plan
(
plan_handle
, { statement_start_offset | 0 | DEFAULT }
, { statement_end_offset | -1 | DEFAULT }
)
Argumentos
plan_handle
Identifica de forma exclusiva un plan de consulta para un lote que está almacenado en caché o ejecutándose. plan_handle es de tipo varbinary(64).El identificador del plan puede obtenerse de los siguientes objetos de administración dinámica:
statement_start_offset | 0 | DEFAULT
Indica, en bytes, la posición inicial de la consulta que la fila describe en el texto del lote o del objeto permanente. statement_start_offset es de tipo int. El valor 0 indica el principio del lote. El valor predeterminado es 0.El desplazamiento inicial de la instrucción puede obtenerse de los siguientes objetos de administración dinámica:
statement_end_offset | -1 | DEFAULT
Indica, en bytes, la posición final de la consulta que la fila describe en el texto del lote o del objeto permanente.statement_start_offset es de tipo int.
El valor -1 indica el final del lote. El valor predeterminado es -1.
Tabla devuelta
Nombre de 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. |
number |
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. |
encrypted |
bit |
Indica si el procedimiento almacenado correspondiente está cifrado. 0 = no cifrado 1 = cifrado La columna no acepta valores NULL. |
query_plan |
nvarchar(max) |
Contiene la representación del plan de presentación de tiempo de compilación del plan de ejecución de consultas especificado con plan_handle. El plan de presentación está en formato de texto. 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. |
Comentarios
En las siguientes condiciones, no se devuelve ningún plan de presentación en la columna plan de la tabla devuelta para sys.dm_exec_text_query_plan:
Si el plan de consulta especificado mediante plan_handle se ha desalojado 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_text_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 de dichas instrucciones no se pueden recuperar mediante sys.dm_exec_text_query_plan 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_text_query_plan correspondiente al procedimiento almacenado o lote. En cambio, deberá realizar una llamada independiente a sys.dm_exec_text_query_plan para el valor plan_handle 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_text_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.
Permisos
Para ejecutar sys.dm_exec_text_query_plan, un usuario debe ser miembro del rol fijo de servidor sysadmin o disponer del permiso VIEW SERVER STATE en el servidor.
Ejemplos
A.Recuperar el plan de consulta en la memoria caché de una consulta o proceso por lotes de Transact-SQL de ejecución lenta
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 de una consulta o lote de ejecución lenta.
[!NOTA]
Para ejecutar este ejemplo, reemplace los valores de session_id y plan_handle por los valores específicos de su servidor.
Primero, recupere el SPID (Id. de proceso del servidor) para el proceso que está ejecutando la consulta o lote 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 que devuelve sys.dm_exec_requests indica que el identificador del plan para la consulta o el lote de ejecución lenta es 0x06000100A27E7C1FA821B10600. En el ejemplo siguiente se devuelve el plan de consulta del identificador del plan especificado y se usan los valores predeterminados 0 y -1 para devolver todas las instrucciones en la consulta o el lote.
USE master;
GO
SELECT query_plan
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);
GO
B.Recuperar todos los planes de consulta de caché del plan
Para recuperar una instantánea de todos los planes de consulta que residen en la memoria caché de plan, recupere los identificadores de todos los planes de consulta de caché; para ello, consulte la vista de administración dinámica sys.dm_exec_cached_plans. Los identificadores de los planes se almacenan en la columna plan_handle de sys.dm_exec_cached_plans. Después, utilice el operador CROSS APPLY para pasar los identificadores del plan a sys.dm_exec_text_query_plan como se indica a continuación. La salida del plan de presentación de todos los planes almacenados actualmente en la memoria caché de 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_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);
GO
C.Recuperar todos los planes de consulta para los que el servidor ha reunido estadísticas de consultas procedentes de la memoria caché de 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 de los planes se almacenan en la columna plan_handle de sys.dm_exec_query_stats. Después, utilice el operador CROSS APPLY para pasar los identificadores del plan a sys.dm_exec_text_query_plan como se indica a continuación. La salida del plan de presentación de cada plan está en la columna query_plan de la tabla que se devuelve.
USE master;
GO
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);
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 de consulta y el promedio de tiempo de CPU de las cinco mejores consultas. La función sys.dm_exec_text_query_plan especifica los valores predeterminados 0 y -1 para devolver todas las instrucciones del lote del plan de consulta.
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_text_query_plan(qs.plan_handle, 0, -1)
ORDER BY total_worker_time/execution_count DESC;
GO