DBAtlas · Atlas de operaciones de bases de datos

Encontrar las consultas más costosas en la plan cache (CPU, lecturas, duración)

SQL ServerRendimiento

Comando

SELECT TOP (20)
       qs.query_hash,
       SUM(qs.execution_count)                                       AS execs,
       SUM(qs.total_worker_time) / 1000                              AS total_cpu_ms,
       SUM(qs.total_worker_time) / SUM(qs.execution_count) / 1000    AS avg_cpu_ms,
       SUM(qs.total_elapsed_time) / SUM(qs.execution_count) / 1000   AS avg_ms,
       SUM(qs.total_logical_reads) / SUM(qs.execution_count)         AS avg_reads,
       SUM(qs.total_grant_kb) / SUM(qs.execution_count)              AS avg_grant_kb,
       COUNT(DISTINCT qs.query_plan_hash)                            AS plan_count,
       MAX(qs.last_execution_time)                                   AS last_exec,
       MIN(SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
           ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
             ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1)) AS statement_text,
       MIN(DB_NAME(pa.dbid))                                         AS db
FROM   sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
  OUTER APPLY (SELECT CONVERT(int, value) AS dbid FROM sys.dm_exec_plan_attributes(qs.plan_handle)
               WHERE attribute = N'dbid') pa
GROUP BY qs.query_hash
ORDER BY total_cpu_ms DESC;

-- To rank by reads: ORDER BY SUM(qs.total_logical_reads) DESC
-- Plan: SELECT query_plan FROM sys.dm_exec_query_plan(<plan_handle>);

Qué hace

Agrupa las sentencias de la plan cache por query_hash (las consultas ad hoc que solo difieren en literales se reducen a una fila) y devuelve el total y la media de CPU, duración, lecturas lógicas, memory grant y número de planes.

Cuándo usarlo

Cuando la CPU está al máximo, ante una queja de "el servidor va lento", para bases de datos sin Query Store o cuando necesita una vista de todo el servidor a través de todas las bases de datos.

Precaución

Solo ve los planes que siguen en caché: faltan las consultas cuyos planes se expulsaron por reinicio, DBCC FREEPROCCACHE, presión de memoria o RECOMPILE. Use Query Store para un análisis persistente y por ventanas de tiempo. plan_count > 1 sugiere sensibilidad a parámetros o distintas opciones SET. Con una plan cache muy grande la consulta puede tardar unos segundos; segura.

Abrir en DBAtlas →

Relacionado