Explorar guías rápidas · SQL Server
Encontrar consultas de larga duración
Identifica las solicitudes de larga duración y las consultas lentas históricas en SQL Server, separa el tiempo transcurrido del tiempo de CPU y determina si el retraso se debe a esperas, bloqueos, lecturas o una regresión del plan.
En esta página
- Problema
- Respuesta rápida
- Código / comandos
- 1. Encontrar las solicitudes de usuario activas que más tiempo llevan en ejecución
- 2. Mostrar la instrucción que se está ejecutando dentro del lote
- 3. Separar las solicitudes con mucho CPU de las dominadas por esperas
- 4. Encontrar solicitudes bloqueadas de larga duración
- 5. Encontrar solicitudes de larga duración con muchas lecturas lógicas
- 6. Encontrar consultas en caché históricamente lentas
- 7. Encontrar las consultas con el mayor tiempo transcurrido máximo
- 8. Agrupar las consultas lentas por query hash
- 9. Usar Query Store para un análisis histórico duradero de consultas lentas
- 10. Comparar los planes de una consulta lenta
- Qué significa el resultado
- Qué revisar
- Tiempo transcurrido alto + CPU bajo
- Tiempo transcurrido alto + CPU alto
- Tiempo transcurrido alto + lecturas lógicas altas
- blocking_session_id > 0
- Gran diferencia entre la duración mínima y la máxima
- Query Store muestra la misma consulta con varios planes
- Notas de producción
- Larga duración no significa algo malo
- Los datos activos y los históricos provienen de fuentes distintas
- El historial de la caché de planes es transitorio
- Los promedios pueden ocultar valores atípicos
- El tiempo transcurrido incluye las esperas
- Los permisos varían según la versión
- Errores comunes
- 1. Llamar «el problema» a la consulta más larga
- 2. Tratar el tiempo transcurrido como tiempo de CPU
- 3. Fijarse solo en los promedios
- 4. Ignorar el número de ejecuciones
- 5. Terminar una sesión de larga duración sin contexto
- 6. Suponer que una consulta lenta siempre necesita un índice
- 7. Usar las estadísticas de la caché de planes como historial duradero
- Siguiente paso
- Para profundizar
Problema
Los usuarios informan que una consulta, un informe, una llamada a una API o un proceso por lotes tarda mucho más de lo esperado.
La primera pregunta no es simplemente:
¿Qué consulta lleva más tiempo en ejecución?
Las preguntas útiles son:
- ¿La consulta sigue en ejecución?
- ¿Está consumiendo CPU activamente o está mayormente en espera?
- ¿Está bloqueada?
- ¿Está haciendo una cantidad excesiva de lecturas?
- ¿El tiempo de ejecución es anormal para esta consulta?
- ¿Cambió el plan?
- ¿Es una ejecución lenta aislada o un patrón constante?
Respuesta rápida
Para las consultas que se están ejecutando ahora mismo, usa sys.dm_exec_requests.
SELECT TOP (50)
r.session_id,
DB_NAME(r.database_id) AS database_name,
r.status,
r.command,
r.start_time,
CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
r.cpu_time AS cpu_ms,
r.logical_reads,
r.reads,
r.writes,
r.wait_type,
r.wait_time AS wait_ms,
r.wait_resource,
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.total_elapsed_time DESC;
Después, pregúntate por qué la solicitud es lenta:
- CPU alto → ruta de CPU / plan de consulta
- lecturas altas → ruta de acceso a los datos
blocking_session_id > 0→ ruta de bloqueoswait_typerelevante → ruta de análisis de esperas- ya no hay nada activo → ruta de Query Store / historial
Código / comandos
1. Encontrar las solicitudes de usuario activas que más tiempo llevan en ejecución
SELECT TOP (50)
r.session_id,
DB_NAME(r.database_id) AS database_name,
r.status,
r.command,
r.start_time,
CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
r.cpu_time AS cpu_ms,
r.logical_reads,
r.reads,
r.writes,
r.wait_type,
r.wait_time AS wait_ms,
r.wait_resource,
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.total_elapsed_time DESC;
Esta es la consulta de triaje en vivo predeterminada.
No trates automáticamente la primera fila como la causa raíz.
Una solicitud puede tardar mucho porque está:
- bloqueada
- esperando E/S
- esperando CPU
- procesando intencionadamente una carga de trabajo grande
- atascada detrás de otra dependencia
2. Mostrar la instrucción que se está ejecutando dentro del lote
Un procedimiento almacenado de larga duración puede contener muchas instrucciones.
Usa los offsets de la solicitud para aislar la instrucción actual.
SELECT
r.session_id,
r.start_time,
CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
r.cpu_time AS cpu_ms,
r.logical_reads,
r.wait_type,
r.blocking_session_id,
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.total_elapsed_time DESC;
Normalmente esto es más útil que mirar solo el texto completo del procedimiento.
3. Separar las solicitudes con mucho CPU de las dominadas por esperas
SELECT
r.session_id,
r.total_elapsed_time AS elapsed_ms,
r.cpu_time AS cpu_ms,
r.wait_time AS wait_ms,
r.wait_type,
r.blocking_session_id,
CAST(
100.0 * r.cpu_time
/ NULLIF(r.total_elapsed_time, 0)
AS decimal(6,2)
) AS cpu_to_elapsed_pct,
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.total_elapsed_time DESC;
Usa la proporción solo como ayuda para el triaje.
No definas un umbral fijo.
Las consultas en paralelo pueden consumir CPU en varios workers, y el tiempo transcurrido incluye las esperas.
4. Encontrar solicitudes bloqueadas de larga duración
SELECT
r.session_id AS blocked_session_id,
r.blocking_session_id,
DB_NAME(r.database_id) AS database_name,
r.status,
r.wait_type,
r.wait_time AS wait_ms,
r.wait_resource,
CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
txt.text AS blocked_batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
r.blocking_session_id > 0
ORDER BY
r.total_elapsed_time DESC;
Si una solicitud de larga duración está bloqueada, ocúpate primero de la ruta del bloqueador.
Puede que la consulta lenta solo sea la víctima.
5. Encontrar solicitudes de larga duración con muchas lecturas lógicas
SELECT TOP (50)
r.session_id,
CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
r.logical_reads,
r.reads AS physical_reads,
r.cpu_time AS cpu_ms,
r.wait_type,
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.logical_reads DESC;
Esto ayuda a identificar consultas que son lentas porque acceden a muchas páginas.
A continuación, inspecciona el plan de ejecución.
6. Encontrar consultas en caché históricamente lentas
sys.dm_exec_query_stats es útil para consultas completadas recientemente cuyos planes siguen en caché.
SELECT TOP (50)
qs.execution_count,
qs.creation_time,
qs.last_execution_time,
CAST(
qs.total_elapsed_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_elapsed_ms,
CAST(qs.max_elapsed_time / 1000.0 AS decimal(18,2)) AS max_elapsed_ms,
CAST(qs.min_elapsed_time / 1000.0 AS decimal(18,2)) AS min_elapsed_ms,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_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
WHERE
qs.execution_count > 0
ORDER BY
avg_elapsed_ms DESC;
Usa esto cuando la solicitud activa ya no existe pero el historial de la caché de planes sigue disponible.
7. Encontrar las consultas con el mayor tiempo transcurrido máximo
Los promedios pueden ocultar ejecuciones lentas intermitentes.
SELECT TOP (50)
qs.execution_count,
CAST(qs.max_elapsed_time / 1000.0 AS decimal(18,2)) AS max_elapsed_ms,
CAST(qs.min_elapsed_time / 1000.0 AS decimal(18,2)) AS min_elapsed_ms,
CAST(
qs.total_elapsed_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_elapsed_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.max_elapsed_time DESC;
Una gran diferencia entre la duración mínima y la máxima es una señal que vale la pena investigar.
Las posibles causas incluyen:
- sensibilidad a parámetros
- bloqueos durante algunas ejecuciones
- cambios de plan
- cambios en el volumen de datos
- contención de recursos
8. Agrupar las consultas lentas por query hash
Las diferencias de literales en consultas ad hoc pueden fragmentar las estadísticas de la caché de planes.
SELECT TOP (50)
qs.query_hash,
SUM(qs.execution_count) AS executions,
CAST(
SUM(qs.total_elapsed_time) * 1.0
/ NULLIF(SUM(qs.execution_count), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_elapsed_ms,
CAST(
MAX(qs.max_elapsed_time) / 1000.0
AS decimal(18,2)
) AS max_elapsed_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
weighted_avg_elapsed_ms DESC;
Usa esto para agregar formas de consulta lógicamente equivalentes.
9. Usar Query Store para un análisis histórico duradero de consultas lentas
Si Query Store está habilitado:
SELECT TOP (50)
q.query_id,
p.plan_id,
qt.query_sql_text,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_duration * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_duration_ms,
CAST(
MAX(rs.max_duration) / 1000.0
AS decimal(18,2)
) AS max_duration_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
weighted_avg_duration_ms DESC;
Usa Query Store para:
- consultas lentas intermitentes
- regresiones de plan
- análisis de antes y después
- historial tras la rotación de la caché de planes
10. Comparar los planes de una consulta lenta
DECLARE @query_id bigint = 123;
SELECT
p.plan_id,
p.is_forced_plan,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_duration * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_duration_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_plan AS p
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
WHERE
p.query_id = @query_id
GROUP BY
p.plan_id,
p.is_forced_plan
ORDER BY
weighted_avg_duration_ms DESC;
Si un plan es sistemáticamente más lento que otro, investiga una regresión del plan en lugar de tratar solo el texto de la consulta como el problema.
Qué significa el resultado
| Campo | Por qué importa |
|---|---|
total_elapsed_time |
Tiempo real transcurrido de la solicitud activa. Incluye las esperas. |
cpu_time |
CPU consumido por la solicitud activa. |
wait_time |
Duración de la espera actual de la solicitud. |
wait_type |
Categoría de la espera actual. A menudo es la pista más rápida para entender por qué el tiempo transcurrido es alto. |
blocking_session_id |
Sesión que está bloqueando la solicitud, si la hay. |
logical_reads |
Páginas leídas a través del buffer pool. Los valores altos pueden indicar un acceso amplio. |
avg_elapsed_ms |
Tiempo transcurrido promedio de las ejecuciones completadas en caché. |
max_elapsed_ms |
Ejecución completada más lenta observada en el ciclo de vida actual de las estadísticas. |
min_elapsed_ms |
Ejecución completada más rápida observada en el ciclo de vida actual de las estadísticas. |
query_hash |
Útil para agrupar consultas lógicamente equivalentes. |
plan_id |
Identificador del plan en Query Store para comparar regresiones. |
Qué revisar
Tiempo transcurrido alto + CPU bajo
Suele ser un problema de esperas, no de CPU.
Revisa:
- bloqueos
- esperas de E/S
- concesiones de memoria (memory grants)
- consumo por parte de la red o del cliente
- disponibilidad de workers
Tiempo transcurrido alto + CPU alto
Es posible que la consulta sea realmente costosa.
Inspecciona:
- el plan de ejecución
- los recorridos (scans)
- las combinaciones (joins)
- las ordenaciones
- las agregaciones
- las estimaciones de cardinalidad
- el comportamiento de los parámetros
Tiempo transcurrido alto + lecturas lógicas altas
Es posible que la consulta esté procesando más páginas de las necesarias.
Busca:
- recorridos amplios
- predicados deficientes
- combinaciones poco selectivas
- índices faltantes o ineficaces
- conjuntos de resultados grandes esperados
blocking_session_id > 0
Sigue al bloqueador.
No optimices primero la consulta bloqueada, a menos que la propia consulta también sea problemática.
Gran diferencia entre la duración mínima y la máxima
Esto puede indicar un comportamiento inestable.
Posibles causas:
- sensibilidad a parámetros
- cambios de plan
- bloqueos irregulares
- cambios en la carga de trabajo
- sesgo de datos (data skew)
Query Store muestra la misma consulta con varios planes
Compara los planes por número de ejecuciones, duración promedio, CPU y lecturas.
Un plan más lento que aparece después de un despliegue o de cambios en estadísticas o índices es una prueba sólida de regresión.
Notas de producción
Larga duración no significa algo malo
Algunas operaciones son largas por diseño:
- ETL
- mantenimiento de índices
- copias de seguridad
- cargas de datos
- informes grandes
- comprobaciones de integridad
La pregunta es si la duración es la esperada para la carga de trabajo y el SLA.
Los datos activos y los históricos provienen de fuentes distintas
Usa:
sys.dm_exec_requests→ solicitudes activassys.dm_exec_query_stats→ estadísticas de consultas completadas en caché- Query Store → historial persistente de métricas de ejecución y planes
No los mezcles como si representaran la misma ventana de observación.
El historial de la caché de planes es transitorio
Las filas de sys.dm_exec_query_stats desaparecen cuando el plan sale de la caché.
Usa Query Store para un análisis histórico duradero.
Los promedios pueden ocultar valores atípicos
Una consulta con un promedio de 100 ms puede tardar ocasionalmente 30 segundos.
Revisa:
- la duración máxima
- la duración mínima
- el historial de planes
- los datos por ventana de tiempo
cuando la latencia intermitente sea importante.
El tiempo transcurrido incluye las esperas
No compares el tiempo transcurrido y el CPU como si fueran la misma métrica.
Una consulta puede pasar la mayor parte de su vida esperando.
Los permisos varían según la versión
Para un acceso amplio a las DMV de rendimiento:
- SQL Server 2019 y versiones anteriores suelen requerir
VIEW SERVER STATE - SQL Server 2022 y versiones posteriores suelen requerir
VIEW SERVER PERFORMANCE STATE
Azure SQL Database tiene reglas de ámbito y permisos diferentes.
Errores comunes
1. Llamar «el problema» a la consulta más larga
La solicitud que más tiempo lleva en ejecución puede estar simplemente esperando detrás de otra sesión.
Sigue primero las esperas y los bloqueos.
2. Tratar el tiempo transcurrido como tiempo de CPU
El tiempo transcurrido incluye las esperas.
Una consulta de 60 segundos puede haber usado muy poco CPU.
3. Fijarse solo en los promedios
Los promedios pueden ocultar regresiones puntuales o específicas de ciertos parámetros.
Revisa los valores mínimos y máximos y el historial de Query Store.
4. Ignorar el número de ejecuciones
Una consulta que se ejecuta una vez al día puede ser lenta pero tener poco impacto.
Una consulta que se ejecuta miles de veces puede generar más carga de trabajo acumulada.
5. Terminar una sesión de larga duración sin contexto
Antes de terminarla, entiende:
- la operación de negocio
- el estado de la transacción
- el costo del rollback
- el impacto en los bloqueos
6. Suponer que una consulta lenta siempre necesita un índice
La causa raíz puede ser:
- un bloqueo
- un plan deficiente
- estadísticas desactualizadas
- presión de CPU
- presión de memoria
- lentitud del cliente
- latencia del almacenamiento
7. Usar las estadísticas de la caché de planes como historial duradero
No son permanentes.
Usa Query Store o un monitoreo externo para obtener evidencia histórica.
¿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.