sys.dm_exec_query_plan_stats (Transact-SQL)

Article
09/07/2023

Se aplica a: SQL Server 2019 (15.x) Base de datos de Azure SQL Azure SQL Managed Instance

Devuelve el equivalente del último plan de ejecución real conocido para un plan de consulta previamente almacenado en caché.

Sintaxis

sys.dm_exec_query_plan_stats ( 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 puede obtenerse 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.
number	smallint	Entero de procedimiento almacenado numerado. Por ejemplo, un grupo de procedimientos para la aplicación de pedidos puede denominarse 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	xml	Contiene la última representación conocida del plan de presentación de tiempo de ejecución del plan de ejecución de consultas real especificado 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.

Comentarios

Esta característica es opcional. Para habilitar en el nivel de servidor, use la marca de seguimiento 2451. En el nivel de base de datos, use la opción LAST_QUERY_PLAN_STATS en ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Esta función del sistema funciona en la infraestructura ligera de generación de perfiles de estadísticas de ejecución de consultas. Para obtener más información, vea Infraestructura de generación de perfiles de consultas.

La salida del plan de presentación de sys.dm_exec_query_plan_stats contiene la siguiente información:

Toda la información en tiempo de compilación que se encuentra en el plan almacenado en caché.
Información en tiempo de ejecución, como el número real de filas por operador, el tiempo total de CPU de la consulta y el tiempo de ejecución, las advertencias de desbordamiento, el DOP real, la memoria máxima usada y la memoria concedida.

En las condiciones siguientes, se devuelve una salida del plan de presentación equivalente a un plan de ejecución real en la query_plan columna de la tabla devuelta para sys.dm_exec_query_plan_stats:

El plan se puede encontrar en sys.dm_exec_cached_plans.

AND
La consulta que se ejecuta es compleja o consume recursos.

En las condiciones siguientes, se devuelve una salida simplificada ¹ del plan de presentación en la columna query_plan de la tabla devuelta para sys.dm_exec_query_plan_stats:

El plan se puede encontrar en sys.dm_exec_cached_plans.

AND
La consulta es lo suficientemente sencilla, normalmente clasificada como parte de una carga de trabajo OLTP.

¹ Hace referencia a un plan de presentación que solo contiene el operador de nodo raíz (SELECT).

En las condiciones siguientes, no se devuelve ningún resultado de sys.dm_exec_query_plan_stats:

El plan de consulta especificado mediante plan_handle se ha expulsado de la memoria caché del plan.

OR
El plan de consulta no se puede almacenar en caché en primer lugar. Para obtener más información, consulte Reutilización y caché de un plan de ejecución.

Nota:

Una limitación en el número de niveles anidados permitidos en el tipo de datos xml significa que sys.dm_exec_query_plan no puede devolver planes de consulta que cumplan o superen 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.

Permisos

Requiere el permiso VIEW SERVER STATE en el servidor.

Permisos para SQL Server 2022 y versiones posteriores

Requiere el permiso VER ESTADO DE RENDIMIENTO DEL SERVIDOR en el servidor.

Ejemplos

A Examine el último plan de ejecución de consultas real conocido para un plan en caché específico.

En el siguiente ejemplo se consulta sys.dm_exec_cached_plans para buscar el plan de interés y copiar su plan_handle desde la salida.

SELECT * FROM sys.dm_exec_cached_plans;
GO

A continuación, para obtener el último plan de ejecución de consultas real conocido, use el valor de plan_handle copiado con la función del sistema sys.dm_exec_query_plan_stats.

SELECT * FROM sys.dm_exec_query_plan_stats(< copied plan_handle >);
GO

B. Examine el último plan de ejecución de consultas real conocido para todos los planes almacenados en caché.

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;
GO

C. Examine el último plan de ejecución de consultas real conocido para un plan almacenado en caché y un texto de consulta específicos.

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps
WHERE st.text LIKE 'SELECT * FROM Person.Person%';
GO

D. Examine los eventos almacenados en caché para el desencadenador.

SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle)
WHERE objtype ='Trigger';
GO

Comparteix a través de