Explorar guías rápidas · SQL Server

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

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
  1. Problema
  2. Respuesta rápida
  3. Código / comandos
  4. 1. Encontrar las solicitudes de usuario activas que más tiempo llevan en ejecución
  5. 2. Mostrar la instrucción que se está ejecutando dentro del lote
  6. 3. Separar las solicitudes con mucho CPU de las dominadas por esperas
  7. 4. Encontrar solicitudes bloqueadas de larga duración
  8. 5. Encontrar solicitudes de larga duración con muchas lecturas lógicas
  9. 6. Encontrar consultas en caché históricamente lentas
  10. 7. Encontrar las consultas con el mayor tiempo transcurrido máximo
  11. 8. Agrupar las consultas lentas por query hash
  12. 9. Usar Query Store para un análisis histórico duradero de consultas lentas
  13. 10. Comparar los planes de una consulta lenta
  14. Qué significa el resultado
  15. Qué revisar
  16. Tiempo transcurrido alto + CPU bajo
  17. Tiempo transcurrido alto + CPU alto
  18. Tiempo transcurrido alto + lecturas lógicas altas
  19. blocking_session_id > 0
  20. Gran diferencia entre la duración mínima y la máxima
  21. Query Store muestra la misma consulta con varios planes
  22. Notas de producción
  23. Larga duración no significa algo malo
  24. Los datos activos y los históricos provienen de fuentes distintas
  25. El historial de la caché de planes es transitorio
  26. Los promedios pueden ocultar valores atípicos
  27. El tiempo transcurrido incluye las esperas
  28. Los permisos varían según la versión
  29. Errores comunes
  30. 1. Llamar «el problema» a la consulta más larga
  31. 2. Tratar el tiempo transcurrido como tiempo de CPU
  32. 3. Fijarse solo en los promedios
  33. 4. Ignorar el número de ejecuciones
  34. 5. Terminar una sesión de larga duración sin contexto
  35. 6. Suponer que una consulta lenta siempre necesita un índice
  36. 7. Usar las estadísticas de la caché de planes como historial duradero
  37. Siguiente paso
  38. 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 bloqueos
  • wait_type relevante → 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 activas
  • sys.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.

Siguiente paso

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

  • Bloqueada → encuentra el bloqueador principal e inspecciona las transacciones abiertas.
  • CPU alto → inspecciona el plan de ejecución actual o anterior y la carga de trabajo con mucho CPU.
  • Lecturas altas → inspecciona los recorridos, los predicados, las combinaciones y la indexación.
  • Tipo de espera relevante → sigue la categoría de espera.
  • Gran diferencia entre la duración mínima y la máxima → investiga la sensibilidad a parámetros y la estabilidad del plan.
  • El plan cambió cuando se degradó el rendimiento → compara los planes en Query Store.
  • El problema ya desapareció → usa Query Store y el monitoreo persistido.
  • La larga duración es esperada → valida el SLA y el impacto en los recursos antes de optimizar.

← Todas las guías rápidas