Encontrar consultas con mayor consumo total de CPU

Señal o problema observado

El uso de CPU es elevado o presenta picos repetidos. Antes de revisar planes al azar, necesitamos localizar las sentencias terminadas que más CPU han acumulado entre los planes todavía presentes en caché.

Qué necesitamos distinguir

El consumo total favorece consultas ejecutadas muchas veces; el promedio ayuda a detectar ejecuciones individualmente costosas. Ambas medidas pertenecen al ciclo de vida del plan almacenado y no constituyen un historial permanente del servidor.

Consulta

DECLARE @Cantidad INT = 25;

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_worker_time / 1000.0) AS CpuTotalMs,
    CONVERT
    (
        DECIMAL(18, 2),
        qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000.0
    ) AS CpuPromedioMs,
    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 > 0
ORDER BY
    qs.total_worker_time DESC,
    qs.execution_count DESC;

Parámetros o permisos

@Cantidad limita la revisión inicial. La consulta requiere VIEW SERVER STATE, o VIEW SERVER PERFORMANCE STATE desde SQL Server 2022. El tiempo de CPU se reporta en microsegundos, con precisión de milisegundos, y aquí se convierte a milisegundos.

Una misma construcción lógica puede aparecer en varias filas por recompilaciones, parámetros, bases de contexto o planes distintos. Los objetos ad hoc pueden no tener nombre de esquema u objeto.

Cómo leer el resultado

Empieza por CpuTotalMs para reconocer impacto acumulado. Después contrástalo con Ejecuciones y CpuPromedioMs: una sentencia barata pero muy frecuente puede superar a otra cara que se ejecuta pocas veces.

PlanCreado delimita desde cuándo se han acumulado los contadores de esa fila. Sentencia extrae el fragmento correspondiente dentro del lote, no necesariamente el lote completo.

Qué conclusión sí permite obtener

Permite priorizar sentencias terminadas cuyos planes siguen en caché y que han concentrado más CPU total durante la vida de esos planes.

Qué conclusión todavía no permite obtener

No muestra consultas en curso, entradas expulsadas de caché ni consumo anterior a una recompilación. Un total alto tampoco prueba que la sentencia esté mal diseñada: puede corresponder a una carga necesaria y frecuente.

Siguiente comprobación razonable

Contrasta las candidatas con sus lecturas, duración y frecuencia. Para una sentencia concreta, recupera el plan almacenado y confirma el comportamiento mediante una ejecución controlada antes de proponer cambios.