Find queries with longer average duration

Users report slow responses, but the total consumption hides infrequent operations. We need to locate statements whose average elapsed time is high among the plans still present in cache.

What we need to distinguish

Elapsed time measures the complete interval between start and finish. CPU time counts processor work only; the difference may include waits for blocking, I/O, or other resources.

Elapsed duration includes execution and pauses for resources; It is not the same as CPU. An average reduces all executions of the plan to a single value and can hide important differences between the best, last, and worst execution.

Query

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Consulta
DECLARE @Cantidad INT = 25;
DECLARE @MinimoEjecuciones BIGINT = 5;

SELECT TOP (@Cantidad)
    DB_NAME(st.dbid) AS BaseDatos,
    OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS Esquema,
    OBJECT_NAME(st.objectid, st.dbid) AS Objeto,
    qs.execution_count AS Ejecuciones,
    CONVERT
    (
        DECIMAL(18, 2),
        qs.total_elapsed_time / NULLIF(qs.execution_count, 0) / 1000.0
    ) AS DuracionPromedioMs,
    CONVERT(DECIMAL(18, 2), qs.min_elapsed_time / 1000.0) AS DuracionMinimaMs,
    CONVERT(DECIMAL(18, 2), qs.max_elapsed_time / 1000.0) AS DuracionMaximaMs,
    CONVERT(DECIMAL(18, 2), qs.last_elapsed_time / 1000.0) AS UltimaDuracionMs,
    qs.creation_time AS PlanCreado,
    qs.last_execution_time AS UltimaEjecucion,
    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 Sentencia
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.execution_count >= @MinimoEjecuciones
ORDER BY
    qs.total_elapsed_time / NULLIF(qs.execution_count, 0) DESC,
    qs.execution_count DESC;
GO

Parameters or permissions

@MinimoEcuciones prevents a single execution from accidentally leading the average; adjust it to the load frequency. @Quantity limits the list. VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE, is required from SQL Server 2022. Times are reported in microseconds, with millisecond precision, and are converted here to milliseconds. The row only collects runs completed during the life of the cached plan.

How to read the result

Compare the average with minimum, maximum and last duration. A large separation suggests variability that may depend on parameters, locks, concurrency, data volume, or server conditions. High duration with moderate CPU can point to waiting time. A high duration and CPU guide towards intensive work, but still require reviewing the plan and context.

What this does establish

It allows you to find repeated sentences whose average time is high within the available window and recognize whether their durations have been stable or variable.

What this still does not establish

It does not preserve each individual execution, does not explain outliers, and does not itself separate CPU, blocking, I/O, or network time. It also does not show plans that have already left cache.

Next reasonable check

Compare the candidate with CPU, reads, and active waits. After retrieves plan; if you need interval history, compare windows with Query Store.

Search SQL Server