sys.dm_exec_query_stats (Transact-SQL)

Artículo
04/02/2014

Devuelve estadísticas de rendimiento de agregado para planes de consulta en caché en SQL Server 2012. La vista contiene una fila por cada instrucción de consulta dentro del plan en caché, y la duración de las filas está ligada al propio plan. Cuando se quita un plan de la caché, se eliminan las filas correspondientes de esta vista.

[!NOTA]

Una consulta inicial de sys.dm_exec_query_stats podría producir resultados imprecisos si hay una carga de trabajo ejecutándose actualmente en el servidor. Pueden determinarse resultados más exactos si se vuelve a ejecutar la consulta.

Nombre de columna	Tipo de datos	Descripción
sql_handle	varbinary(64)	Es un token que hace referencia al lote o al procedimiento almacenado del que forma parte la consulta. sql_handle, junto con statement_start_offset y statement_end_offset, se pueden usar para recuperar el texto SQL de la consulta llamando a la función de administración dinámica sys.dm_exec_sql_text.
statement_start_offset	int	Indica (en bytes y empezando por 0) la posición inicial de la consulta que la fila describe en el texto del lote o del objeto persistente.
statement_end_offset	int	Indica (en bytes y empezando por 0) la posición final de la consulta que la fila describe en el texto del lote o del objeto persistente. El valor -1 indica el final del lote.
plan_generation_num	bigint	Número de secuencia que se puede usar para distinguir entre instancias de los planes después de una nueva compilación.
plan_handle	varbinary(64)	Token que hace referencia al plan compilado del que forma parte la consulta. Este valor se puede pasar a la función de administración dinámica sys.dm_exec_query_plan para obtener el plan de consulta.
creation_time	datetime	Hora en que se compiló el plan.
last_execution_time	datetime	Hora a la que se inició la ejecución del plan por última vez.
execution_count	bigint	Número de veces que se ha ejecutado el plan desde que se compiló por última vez.
total_worker_time	bigint	Tiempo total de CPU, notificado en microsegundos (pero solo con precisión de milisegundos), consumido por las ejecuciones de este plan desde su compilación.
last_worker_time	bigint	Tiempo de CPU, notificado en microsegundos (pero solo con precisión de milisegundos), que se consumió la última vez que se ejecutó el plan.
min_worker_time	bigint	Tiempo de CPU mínimo, notificado en microsegundos (pero solo con precisión de milisegundos), que este plan ha consumido alguna vez durante una sola ejecución.
max_worker_time	bigint	Tiempo de CPU máximo, notificado en microsegundos (pero solo con precisión de milisegundos), que este plan ha consumido alguna vez durante una sol ejecución.
total_physical_reads	bigint	Número total de lecturas físicas realizadas por las ejecuciones de este plan desde su compilación.
last_physical_reads	bigint	Número de lecturas físicas realizadas la última vez que se ejecutó el plan.
min_physical_reads	bigint	Número mínimo de lecturas físicas que ha realizado este plan durante una ejecución.
max_physical_reads	bigint	Número máximo de lecturas físicas que ha realizado este plan durante una ejecución.
total_logical_writes	bigint	Número total de escrituras lógicas realizadas por las ejecuciones de este plan desde su compilación.
last_logical_writes	bigint	Número de páginas del grupo de búferes modificadas la última vez que se ejecutó el plan. Si una página ya se ha modificado, no se cuentan las escrituras.
min_logical_writes	bigint	Número mínimo de escrituras lógicas que ha realizado este plan durante una ejecución.
max_logical_writes	bigint	Número máximo de escrituras lógicas que ha realizado este plan durante una ejecución.
total_logical_reads	bigint	Número total de lecturas lógicas realizadas por las ejecuciones de este plan desde su compilación.
last_logical_reads	bigint	Número de lecturas lógicas realizadas la última vez que se ejecutó el plan.
min_logical_reads	bigint	Número mínimo de lecturas lógicas que ha realizado este plan durante una ejecución.
max_logical_reads	bigint	Número máximo de lecturas lógicas que ha realizado este plan durante una ejecución.
total_clr_time	bigint	Tiempo, notificado en microsegundos (pero solo con precisión de milisegundos), consumido en objetos de Common Language Runtime (CLR) de Microsoft .NET Framework mediante las ejecuciones de este plan desde su compilación. Los objetos CLR pueden ser procedimientos almacenados, funciones, desencadenadores, tipos y agregados.
last_clr_time	bigint	Tiempo, notificado en microsegundos (pero solo con precisión de milisegundos), consumido por la ejecución dentro de los objetos CLR de .NET Framework durante la última ejecución de este plan. Los objetos CLR pueden ser procedimientos almacenados, funciones, desencadenadores, tipos y agregados.
min_clr_time	bigint	Tiempo de CPU mínimo, notificado en microsegundos (pero solo con precisión de milisegundos), que este plan ha consumido alguna vez dentro de objetos CLR de .NET Framework durante una sola ejecución. Los objetos CLR pueden ser procedimientos almacenados, funciones, desencadenadores, tipos y agregados.
max_clr_time	bigint	Tiempo de CPU máximo, notificado en microsegundos (pero solo con precisión de milisegundos), que este plan ha consumido alguna vez dentro del CLR de .NET Framework durante una sola ejecución. Los objetos CLR pueden ser procedimientos almacenados, funciones, desencadenadores, tipos y agregados.
total_elapsed_time	bigint	Tiempo total transcurrido, notificado en microsegundos (pero solo con precisión de milisegundos), para las ejecuciones completadas de este plan.
last_elapsed_time	bigint	Tiempo transcurrido, notificado en microsegundos (pero solo con precisión de milisegundos), para la ejecución completada más recientemente de este plan.
min_elapsed_time	bigint	Tiempo mínimo transcurrido, notificado en microsegundos (pero solo con precisión de milisegundos), para cualquier ejecución completada de este plan.
max_elapsed_time	bigint	Tiempo máximo transcurrido, notificado en microsegundos (pero solo con precisión de milisegundos), para cualquier ejecución completada de este plan.
query_hash	Binary(8)	Valor hash binario que se calcula en la consulta y que se usa para identificar consultas con una lógica similar. Puede usar el hash de consulta para determinar el uso de recursos agregados para las consultas que solo se diferencian en los valores literales.
query_plan_hash	binary(8)	Valor hash binario que se calcula en el plan de ejecución de consulta y que se usa para identificar planes de ejecución de consulta similares. Puede usar el hash del plan de consulta para buscar el costo acumulativo de las consultas con planes de ejecución similares.
total_rows	bigint	Número total de filas devueltas por la consulta. No puede ser NULL.
last_rows	bigint	Número de filas devueltas por la última ejecución de la consulta. No puede ser NULL.
min_rows	bigint	Número mínimo de filas devueltas por la consulta el número de veces que se ha ejecutado el plan desde la última vez que se compiló. No puede ser NULL.
max_rows	bigint	Número máximo de filas devueltas por la consulta el número de veces que se ha ejecutado el plan desde la última vez que se compiló. No puede ser NULL.

Permisos

Necesita el permiso VIEW SERVER STATE en el servidor.

Comentarios

Cuando se completa una consulta, se actualizan las estadísticas en la vista.

Ejemplos

A.Buscar las consultas TOP N

En el siguiente ejemplo se devuelve información acerca de las cinco primeras consultas clasificadas por el promedio de tiempo de CPU. En este ejemplo se agregan las consultas según su hash de consulta para que las consultas lógicamente equivalentes se agrupen según su consumo acumulado de recursos.

USE AdventureWorks2012;
GO
SELECT TOP 5 query_stats.query_hash AS "Query Hash", 
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM 
    (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO

B.Devolver agregados de recuentos de filas para una consulta

En el ejemplo siguiente se devuelve información de agregado de recuento de filas (filas totales, filas mínimas, filas máximas y últimas filas) para las consultas.

SELECT qs.execution_count,
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
                 (CASE WHEN qs.statement_end_offset = -1 
                       THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
                       ELSE qs.statement_end_offset end -
                            qs.statement_start_offset
                 )/2
             ) AS query_text, 
     qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid, 
     qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
WHERE qt.text like '%SELECT%' 
ORDER BY qs.execution_count DESC;

Vea también

Referencia

Funciones y vistas de administración dinámica (Transact-SQL)

Funciones y vistas de administración dinámica relacionadas con ejecuciones (Transact-SQL)

sys.dm_exec_sql_text (Transact-SQL)

sys.dm_exec_query_plan (Transact-SQL)

Compartir a través de