Explorar guías rápidas · SQL Server

Tecnología: SQL ServerCategoría: RendimientoNivel: IntermedioActualizado

Encontrar consultas con alto consumo de CPU

Identifica las consultas de SQL Server que más CPU consumen ahora y en el historial, distingue el CPU total del CPU por ejecución y elige el siguiente paso de optimización adecuado.

En esta página
  1. Problema
  2. Respuesta rápida
  3. Código / comandos
  4. 1. Encontrar las solicitudes activas que más CPU consumen
  5. 2. Mostrar la instrucción que se está ejecutando dentro del lote
  6. 3. Encontrar consultas en caché por CPU total
  7. 4. Encontrar consultas con el CPU promedio por ejecución más alto
  8. 5. Encontrar consumidores de CPU de alta frecuencia
  9. 6. Agrupar consultas lógicamente equivalentes por query hash
  10. 7. Mostrar el texto de la consulta y el plan de ejecución de las consultas en caché con alto consumo de CPU
  11. 8. Comparar el tiempo de CPU con el tiempo transcurrido de las solicitudes activas
  12. 9. Comprobar si la presión de CPU va más allá de una sola consulta
  13. 10. Usar Query Store para el historial de CPU alto
  14. Qué significa el resultado
  15. Qué revisar
  16. CPU total alto + número de ejecuciones alto
  17. CPU promedio alto + número de ejecuciones bajo
  18. CPU alto + lecturas lógicas altas
  19. Tiempo transcurrido alto + CPU bajo
  20. Muchas consultas ad hoc lógicamente equivalentes
  21. CPU alto + colas runnable en los schedulers
  22. Notas de producción
  23. sys.dm_exec_query_stats se basa en la caché
  24. Las estadísticas de consultas se actualizan cuando termina la ejecución
  25. Las unidades de CPU difieren entre DMV
  26. Las consultas en paralelo complican la interpretación
  27. Los promedios en caché pueden ocultar sesgos
  28. Los permisos varían según la versión
  29. Un CPU alto puede ser legítimo
  30. Errores comunes
  31. 1. Ordenar solo por CPU promedio
  32. 2. Ordenar solo por CPU total
  33. 3. Tratar el tiempo transcurrido como tiempo de CPU
  34. 4. Usar sys.dm_exec_query_stats para consultas en ejecución
  35. 5. Ignorar el ciclo de vida de la caché de planes
  36. 6. Asumir que muchas lecturas lógicas significan automáticamente un índice faltante
  37. 7. Cambiar MAXDOP porque el CPU está alto
  38. 8. Ignorar la frecuencia de llamadas de la aplicación
  39. Siguiente paso
  40. Para profundizar

Problema

El CPU de SQL Server está elevado, la latencia de la aplicación aumenta o necesitas identificar qué consultas son responsables de la mayor parte del tiempo de procesador.

El objetivo es responder:

  • ¿Qué solicitudes están consumiendo CPU ahora mismo?
  • ¿Qué consultas en caché han consumido más CPU en total?
  • ¿Qué consultas son costosas por ejecución?
  • ¿Es costosa una sola consulta, o una consulta muy frecuente está generando presión de CPU acumulada?
  • ¿Un tiempo transcurrido alto se debe realmente al CPU, o la consulta pasa la mayor parte del tiempo en espera?

Respuesta rápida

Para la actividad de CPU actual, empieza por sys.dm_exec_requests.

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.cpu_time AS cpu_ms,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.logical_reads,
    r.wait_type,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
ORDER BY
    r.cpu_time DESC;

Para la carga de trabajo completada recientemente que está en la caché de planes, usa sys.dm_exec_query_stats.

SELECT TOP (20)
    qs.execution_count,
    CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
    CAST(
        qs.total_worker_time * 1.0
        / NULLIF(qs.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_cpu_ms,
    qs.last_execution_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
    qs.total_worker_time DESC;

Usa ambas vistas juntas:

  • los datos de las solicitudes activas te dicen qué está ocurriendo ahora
  • las estadísticas de consultas te dicen qué carga de trabajo completada recientemente consumió CPU

Código / comandos

1. Encontrar las solicitudes activas que más CPU consumen

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.cpu_time AS cpu_ms,
    r.total_elapsed_time AS elapsed_ms,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.blocking_session_id,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.cpu_time DESC;

Usa esta consulta durante un incidente en curso.

cpu_time son los milisegundos de CPU usados por la solicitud actual.

Un valor alto no significa automáticamente que la solicitud sea la causa raíz, pero es una pista sólida cuando el CPU del servidor también está alto.


2. Mostrar la instrucción que se está ejecutando dentro del lote

Un lote puede contener muchas instrucciones. Usa los offsets de instrucción para aislar la que se está ejecutando en este momento.

SELECT TOP (20)
    r.session_id,
    r.cpu_time AS cpu_ms,
    r.total_elapsed_time AS elapsed_ms,
    r.logical_reads,
    CASE
        WHEN r.statement_start_offset IS NULL THEN txt.text
        ELSE SUBSTRING(
            txt.text,
            (r.statement_start_offset / 2) + 1,
            (
                (
                    CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(txt.text)
                        ELSE r.statement_end_offset
                    END
                    - r.statement_start_offset
                ) / 2
            ) + 1
        )
    END AS statement_text,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.cpu_time DESC;

Esto suele ser más útil que revisar un procedimiento almacenado completo o un lote de varias instrucciones.


3. Encontrar consultas en caché por CPU total

Usa esta consulta para identificar la carga de trabajo que más CPU acumulado ha consumido desde que se compiló el plan en caché.

SELECT TOP (50)
    qs.execution_count,
    qs.creation_time,
    qs.last_execution_time,

    CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,

    CAST(
        qs.total_worker_time * 1.0
        / NULLIF(qs.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_cpu_ms,

    CAST(qs.last_worker_time / 1000.0 AS decimal(18,2)) AS last_cpu_ms,
    qs.total_logical_reads,

    txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
    qs.total_worker_time DESC;

Suele ser la consulta adecuada cuando la pregunta es:

¿Qué carga de trabajo en caché ha consumido más CPU en total?


4. Encontrar consultas con el CPU promedio por ejecución más alto

SELECT TOP (50)
    qs.execution_count,

    CAST(
        qs.total_worker_time * 1.0
        / NULLIF(qs.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_cpu_ms,

    CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
    CAST(qs.max_worker_time / 1000.0 AS decimal(18,2)) AS max_cpu_ms,
    CAST(qs.min_worker_time / 1000.0 AS decimal(18,2)) AS min_cpu_ms,

    qs.last_execution_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
WHERE
    qs.execution_count > 0
ORDER BY
    avg_cpu_ms DESC;

Usa esta consulta para encontrar ejecuciones individuales costosas.

Revisa siempre execution_count junto al promedio.

Una ejecución con 20 segundos de CPU puede importar menos que una consulta de 20 ms ejecutada un millón de veces.


5. Encontrar consumidores de CPU de alta frecuencia

SELECT TOP (50)
    qs.execution_count,

    CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,

    CAST(
        qs.total_worker_time * 1.0
        / NULLIF(qs.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_cpu_ms,

    qs.last_execution_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
WHERE
    qs.execution_count >= 100
ORDER BY
    qs.total_worker_time DESC;

El valor 100 es solo un filtro de ejemplo para reducir el resultado.

No es un umbral universal.

Ajústalo a la carga de trabajo.


6. Agrupar consultas lógicamente equivalentes por query hash

Los literales de las consultas ad hoc pueden crear varias entradas de caché para instrucciones lógicamente equivalentes.

Agrupar por query_hash puede revelar con más claridad el CPU acumulado.

SELECT TOP (50)
    qs.query_hash,
    SUM(qs.execution_count) AS executions,

    CAST(
        SUM(qs.total_worker_time) / 1000.0
        AS decimal(18,2)
    ) AS total_cpu_ms,

    CAST(
        SUM(qs.total_worker_time) * 1.0
        / NULLIF(SUM(qs.execution_count), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_cpu_ms,

    MAX(qs.last_execution_time) AS last_execution_time
FROM sys.dm_exec_query_stats AS qs
GROUP BY
    qs.query_hash
ORDER BY
    total_cpu_ms DESC;

Usa esto cuando las diferencias de literales hagan que la caché parezca fragmentada.

Después, profundiza en el texto de la consulta del hash correspondiente.


7. Mostrar el texto de la consulta y el plan de ejecución de las consultas en caché con alto consumo de CPU

SELECT TOP (20)
    qs.execution_count,
    CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
    CAST(
        qs.total_worker_time * 1.0
        / NULLIF(qs.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_cpu_ms,
    txt.text AS batch_text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
    qs.total_worker_time DESC;

Usa el plan para investigar:

  • scans grandes
  • joins costosos
  • ordenaciones
  • operaciones hash
  • estimaciones deficientes
  • lookups repetidos
  • paralelismo
  • spills o indicadores de presión de memoria

No optimices un plan basándote en un solo operador.


8. Comparar el tiempo de CPU con el tiempo transcurrido de las solicitudes activas

SELECT
    r.session_id,
    r.cpu_time AS cpu_ms,
    r.total_elapsed_time AS elapsed_ms,

    CAST(
        100.0 * r.cpu_time
        / NULLIF(r.total_elapsed_time, 0)
        AS decimal(6,2)
    ) AS cpu_to_elapsed_pct,

    r.status,
    r.wait_type,
    r.blocking_session_id,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.cpu_time DESC;

Esta proporción es solo una señal aproximada de triaje.

El tiempo transcurrido incluye las esperas, y el trabajo en paralelo puede complicar la interpretación.

Úsala para preguntarte:

  • ¿La solicitud está haciendo principalmente trabajo de CPU?
  • ¿O está principalmente en espera?

No uses el porcentaje como un umbral de clasificación estricto.


9. Comprobar si la presión de CPU va más allá de una sola consulta

Las consultas con alto consumo de CPU importan más cuando el scheduler está bajo presión.

SELECT
    scheduler_id,
    cpu_id,
    status,
    is_online,
    current_tasks_count,
    runnable_tasks_count,
    active_workers_count,
    current_workers_count,
    load_factor
FROM sys.dm_os_schedulers
WHERE
    status = N'VISIBLE ONLINE'
ORDER BY
    scheduler_id;

runnable_tasks_count representa los workers listos para ejecutarse pero que esperan CPU del scheduler.

Colas runnable distintas de cero de forma repetida durante un episodio de CPU alto pueden respaldar un diagnóstico de presión de CPU.

No uses una sola muestra instantánea como umbral universal.


10. Usar Query Store para el historial de CPU alto

Si la consulta costosa ya terminó o los datos de la caché de planes son insuficientes, Query Store suele ser la mejor fuente histórica.

SELECT TOP (20)
    q.query_id,
    p.plan_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS executions,

    CAST(
        SUM(rs.avg_cpu_time * rs.count_executions)
        / 1000.0
        AS decimal(18,2)
    ) AS total_cpu_ms,

    CAST(
        SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_cpu_ms
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
GROUP BY
    q.query_id,
    p.plan_id,
    qt.query_sql_text
ORDER BY
    total_cpu_ms DESC;

Es la mejor opción para:

  • regresiones históricas
  • comparación de planes
  • análisis de la carga de trabajo antes/después
  • consultas que ya no están en la caché de planes

Qué significa el resultado

Campo Por qué importa
cpu_time Milisegundos de CPU consumidos por una solicitud activa.
total_worker_time Tiempo total de CPU acumulado por las ejecuciones completadas representadas por la fila de estadísticas de consulta en caché.
last_worker_time CPU consumido por la ejecución completada más reciente representada por la fila de estadísticas.
min_worker_time Tiempo de CPU más bajo observado en una ejecución durante el ciclo de vida actual de la caché.
max_worker_time Tiempo de CPU más alto observado en una ejecución durante el ciclo de vida actual de la caché.
execution_count Número de ejecuciones completadas representadas por la fila de estadísticas en caché.
avg_cpu_ms CPU por ejecución, calculado. Útil solo junto con el número de ejecuciones y el contexto de la carga de trabajo.
query_hash Hash que puede ayudar a agrupar consultas lógicamente equivalentes.
total_elapsed_time Tiempo transcurrido real (wall-clock), incluidas las esperas.
runnable_tasks_count Workers del scheduler listos para ejecutarse pero en espera de CPU.

Qué revisar

CPU total alto + número de ejecuciones alto

Esto suele significar que una consulta «pequeña pero frecuente» está consumiendo una parte importante del CPU.

Posibles siguientes pasos:

  • reducir la frecuencia de ejecución
  • procesar el trabajo por lotes
  • almacenar los resultados en caché
  • mejorar la ruta de acceso
  • reducir las llamadas repetidas de la aplicación

CPU promedio alto + número de ejecuciones bajo

Esto suele apuntar a una operación costosa por sí misma.

Inspecciona:

  • el plan de ejecución
  • los scans
  • los joins
  • las ordenaciones
  • las agregaciones
  • las estimaciones de cardinalidad
  • el comportamiento de los parámetros

CPU alto + lecturas lógicas altas

Esto suele apuntar a un acceso a datos ineficiente.

Posibles causas:

  • scans grandes
  • predicados poco selectivos
  • estrategia de join deficiente
  • índices faltantes o ineficaces
  • estimaciones erróneas

No asumas que la solución es un índice hasta que el plan respalde esa conclusión.

Tiempo transcurrido alto + CPU bajo

Es posible que la consulta no esté limitada por el CPU.

Revisa:

  • las esperas
  • los bloqueos
  • la E/S
  • las concesiones de memoria (memory grants)
  • el consumo del cliente/red

Muchas consultas ad hoc lógicamente equivalentes

Agrupar por query_hash puede revelar que la carga de trabajo es mayor de lo que sugiere cualquier fila individual de la caché.

Esto puede apuntar a problemas de parametrización o de la forma de las consultas generadas por la aplicación.

CPU alto + colas runnable en los schedulers

Esto refuerza la hipótesis de una presión de CPU real.

A continuación, determina si la presión proviene de:

  • una sola consulta
  • muchas consultas concurrentes
  • el paralelismo
  • la compilación
  • el crecimiento del tráfico de la aplicación

Notas de producción

sys.dm_exec_query_stats se basa en la caché

La DMV contiene estadísticas de los planes de consulta en caché.

Cuando el plan sale de la caché, la fila desaparece.

Esto significa que la DMV es útil para analizar la carga de trabajo reciente, pero no es telemetría histórica duradera.

Las estadísticas de consultas se actualizan cuando termina la ejecución

sys.dm_exec_query_stats refleja las ejecuciones de consultas completadas.

Para una consulta que aún se está ejecutando, usa sys.dm_exec_requests.

Las unidades de CPU difieren entre DMV

sys.dm_exec_requests.cpu_time está en milisegundos.

sys.dm_exec_query_stats.total_worker_time se expresa en microsegundos, aunque la precisión subyacente es solo de milisegundos.

Convierte las unidades antes de comparar valores.

Las consultas en paralelo complican la interpretación

El tiempo de CPU puede superar el tiempo transcurrido real cuando el trabajo se realiza en paralelo en varios workers.

No asumas que cpu_time > elapsed_time es un error.

Los promedios en caché pueden ocultar sesgos

Una consulta puede ser barata para la mayoría de los valores de parámetros y muy costosa para unos pocos.

Usa:

  • min_worker_time
  • max_worker_time
  • Query Store
  • el historial de planes
  • un análisis de sensibilidad a parámetros

cuando la distribución importa.

Los permisos varían según la versión

Para un acceso amplio a estas DMV de rendimiento:

  • SQL Server 2019 y versiones anteriores suelen requerir VIEW SERVER STATE
  • SQL Server 2022 y versiones posteriores requieren VIEW SERVER PERFORMANCE STATE

Azure SQL Database tiene reglas de visibilidad y permisos diferentes.

Un CPU alto puede ser legítimo

Una consulta de reporting o un proceso por lotes puede consumir intencionadamente una cantidad importante de CPU.

La pregunta no es solo:

¿Está alto el CPU?

Pregúntate también:

¿El uso de CPU es esperado, eficiente y está dentro del SLA de la carga de trabajo?

Errores comunes

1. Ordenar solo por CPU promedio

Una consulta costosa ejecutada una sola vez puede dominar los promedios y aportar poco a la carga total.

Compara siempre el CPU promedio y el total.

2. Ordenar solo por CPU total

Una consulta de alta frecuencia puede dominar el CPU total y aun así ser eficiente de forma individual.

A veces la mejor solución es reducir el volumen de llamadas, no reescribir el SQL.

3. Tratar el tiempo transcurrido como tiempo de CPU

El tiempo transcurrido incluye las esperas.

Analiza por separado cpu_time, worker_time y las esperas.

4. Usar sys.dm_exec_query_stats para consultas en ejecución

Sus estadísticas se actualizan cuando termina la ejecución.

Usa sys.dm_exec_requests para el trabajo en curso.

5. Ignorar el ciclo de vida de la caché de planes

Un reinicio reciente, una recompilación o la expulsión de un plan acortan la ventana de observación.

No compares totales de la caché sin contexto.

6. Asumir que muchas lecturas lógicas significan automáticamente un índice faltante

Las lecturas son evidencia de trabajo, no un diagnóstico.

Revisa el plan de ejecución real y la carga de trabajo.

7. Cambiar MAXDOP porque el CPU está alto

Puede que el paralelismo esté ayudando a que la consulta termine más rápido.

Investiga los planes, la presión sobre los schedulers, la concurrencia y la carga de trabajo antes de cambiar configuraciones a nivel de instancia.

8. Ignorar la frecuencia de llamadas de la aplicación

Una consulta puede ser perfectamente razonable de forma aislada, pero volverse costosa cuando se llama miles de veces por segundo.

El diagnóstico de CPU debe incluir el volumen de la carga de trabajo.

Siguiente paso

Usa el patrón de CPU para elegir la siguiente investigación.

  • CPU activo alto en una solicitud → inspecciona su instrucción actual y su plan de ejecución.
  • CPU total alto + número de ejecuciones alto → revisa la frecuencia de llamadas y optimiza la ruta crítica.
  • CPU promedio alto + número de ejecuciones bajo → inspecciona el plan de esa consulta y su patrón de acceso a datos.
  • CPU alto + muchas lecturas → investiga scans, joins, estimaciones e indexación.
  • Tiempo transcurrido alto + CPU bajo → pasa al análisis de esperas y bloqueos.
  • Muchas variantes ad hoc → agrupa por query_hash e investiga la parametrización y la generación de consultas.
  • La DMV en vivo ya no muestra el problema → usa Query Store.
  • Las colas runnable de los schedulers siguen elevadas → investiga la presión de CPU y la concurrencia a nivel de todo el sistema.

← Todas las guías rápidas