Explorar guías rápidas · SQL Server
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
- Problema
- Respuesta rápida
- Código / comandos
- 1. Encontrar las solicitudes activas que más CPU consumen
- 2. Mostrar la instrucción que se está ejecutando dentro del lote
- 3. Encontrar consultas en caché por CPU total
- 4. Encontrar consultas con el CPU promedio por ejecución más alto
- 5. Encontrar consumidores de CPU de alta frecuencia
- 6. Agrupar consultas lógicamente equivalentes por query hash
- 7. Mostrar el texto de la consulta y el plan de ejecución de las consultas en caché con alto consumo de CPU
- 8. Comparar el tiempo de CPU con el tiempo transcurrido de las solicitudes activas
- 9. Comprobar si la presión de CPU va más allá de una sola consulta
- 10. Usar Query Store para el historial de CPU alto
- Qué significa el resultado
- Qué revisar
- CPU total alto + número de ejecuciones alto
- CPU promedio alto + número de ejecuciones bajo
- CPU alto + lecturas lógicas altas
- Tiempo transcurrido alto + CPU bajo
- Muchas consultas ad hoc lógicamente equivalentes
- CPU alto + colas runnable en los schedulers
- Notas de producción
- sys.dm_exec_query_stats se basa en la caché
- Las estadísticas de consultas se actualizan cuando termina la ejecución
- Las unidades de CPU difieren entre DMV
- Las consultas en paralelo complican la interpretación
- Los promedios en caché pueden ocultar sesgos
- Los permisos varían según la versión
- Un CPU alto puede ser legítimo
- Errores comunes
- 1. Ordenar solo por CPU promedio
- 2. Ordenar solo por CPU total
- 3. Tratar el tiempo transcurrido como tiempo de CPU
- 4. Usar sys.dm_exec_query_stats para consultas en ejecución
- 5. Ignorar el ciclo de vida de la caché de planes
- 6. Asumir que muchas lecturas lógicas significan automáticamente un índice faltante
- 7. Cambiar MAXDOP porque el CPU está alto
- 8. Ignorar la frecuencia de llamadas de la aplicación
- Siguiente paso
- 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 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_timemax_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.
¿Te resultó útil este contenido?
¿Qué podríamos mejorar?
Gracias — esto me ayuda a mejorar Javi Builds.
Mantente al día
Recibe solo las novedades de Javi Builds que realmente te interesan.
Revisa tu bandeja de entrada
Te he enviado un enlace de confirmación. Tu suscripción se activará cuando la confirmes.