Find queries with highest total CPU consumption

CPU usage is high or has repeated spikes. Before reviewing random plans, we need to locate the completed statements that have accumulated the most CPU among the plans still present in cache.

What we need to distinguish

CPU is the processor time consumed by the work. The plan cache retains compiled strategies for reuse; when a plan leaves it, its accumulated metrics disappear from this query.

Total consumption favors queries executed many times; averaging helps detect individually costly runs. Both measurements belong to the lifecycle of the stored plan and do not constitute permanent server history.

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;

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;
GO

Parameters or permissions

@Quantity limits the initial review. The query requires VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE from SQL Server 2022. CPU time is reported in microseconds, with millisecond precision, and is converted here to milliseconds. The same logical construction can appear in several rows due to different compilations, parameters, context bases or plans. Ad hoc objects may not have a schema or object name.

How to read the result

Start with CpuTotalMs to recognize cumulative impact. Then contrast it with Executions and CpuAverageMs: a cheap but very frequent statement can outperform another face that is executed rarely. PlanCreated delimits since when the counters of that row have been accumulated. Sentence extracts the corresponding fragment within the batch, not necessarily the entire batch.

What this does establish

It allows you to prioritize completed statements whose plans are still in cache and that have concentrated more total CPU during the life of those plans.

What this still does not establish

It does not show queries in progress, entries evicted from cache, or consumption before a rebuild. A high total also does not prove that the sentence is poorly designed: it may correspond to a necessary and frequent load.

Next reasonable check

Also review queries with longest logical read and longest duration. For a particular candidate, retrieves the stored plan and confirms the behavior with a controlled execution.

Search SQL Server