Explorar guías rápidas · SQL Server

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

Tipos de espera de SQL Server

Usa las estadísticas de espera de SQL Server para identificar en qué se está yendo el tiempo, distinguir las esperas actuales de las acumuladas y elegir la siguiente investigación de rendimiento.

En esta página
  1. Problema
  2. Respuesta rápida
  3. Código / comandos
  4. 1. Mostrar las principales esperas acumuladas
  5. 2. Excluir las esperas inactivas y en segundo plano habituales para el diagnóstico
  6. 3. Mostrar qué está esperando en este momento
  7. 4. Relacionar las esperas actuales con las solicitudes y el texto SQL
  8. 5. Mostrar las estadísticas de espera de una sesión
  9. 6. Comprobar la hora de inicio de SQL Server
  10. 7. Capturar una línea base sin borrar las esperas
  11. 8. Restablecer las estadísticas de espera solo cuando tengas un motivo deliberado
  12. Qué significa el resultado
  13. Qué revisar
  14. Tiempo de espera de señal alto
  15. LCK_M_*
  16. PAGEIOLATCH_*
  17. PAGELATCH_*
  18. SOS_SCHEDULER_YIELD
  19. CXPACKET / CXCONSUMER
  20. WRITELOG
  21. ASYNC_NETWORK_IO
  22. RESOURCE_SEMAPHORE
  23. THREADPOOL
  24. HADR_*
  25. Notas de producción
  26. Las estadísticas de espera muestran síntomas, no causas raíz automáticas
  27. sys.dm_os_wait_stats muestra esperas completadas
  28. Las estadísticas de espera se restablecen al reiniciar o al borrarlas explícitamente
  29. Los permisos varían según la versión
  30. Las esperas en segundo plano varían según el entorno
  31. Los totales de espera necesitan una ventana de tiempo
  32. Errores comunes
  33. 1. Tratar la espera principal como la causa raíz
  34. 2. Borrar las estadísticas de espera antes de capturarlas
  35. 3. Usar una enorme lista de exclusión universal
  36. 4. Confundir PAGEIOLATCH_* y PAGELATCH_*
  37. 5. Suponer que las esperas de paralelismo significan «poner MAXDOP en 1»
  38. 6. Ignorar las esperas de señal
  39. 7. Mirar solo las esperas de toda la instancia
  40. 8. Comparar entornos sin normalizar la carga de trabajo y el tiempo
  41. Siguiente paso
  42. Para profundizar

Problema

SQL Server va lento, pero el CPU, las lecturas, los bloqueos y la duración de las consultas no explican de inmediato por qué.

Las estadísticas de espera ayudan a responder otra pregunta:

¿Qué está esperando SQL Server?

El objetivo no es encontrar un tipo de espera «malo» y corregirlo a ciegas.

El objetivo es:

  • identificar las categorías de espera dominantes
  • distinguir las esperas actuales de las esperas acumuladas históricas
  • separar las esperas de recursos de las esperas de cola y en segundo plano
  • entender si el cuello de botella probablemente está en el CPU, el bloqueo, la E/S, la memoria, el paralelismo, el registro de transacciones, la red u otra cosa
  • elegir el siguiente paso de diagnóstico

Respuesta rápida

Usa sys.dm_os_wait_stats para el historial acumulado de esperas a nivel de instancia.

SELECT TOP (30)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    CAST(
        (wait_time_ms - signal_wait_time_ms) / 1000.0
        AS decimal(18,2)
    ) AS resource_wait_seconds,
    CAST(
        signal_wait_time_ms / 1000.0
        AS decimal(18,2)
    ) AS signal_wait_seconds,
    CAST(
        100.0 * wait_time_ms
        / NULLIF(SUM(wait_time_ms) OVER (), 0)
        AS decimal(6,2)
    ) AS pct_of_total_wait_time
FROM sys.dm_os_wait_stats
WHERE
    wait_time_ms > 0
ORDER BY
    wait_time_ms DESC;

Usa esta consulta para encontrar las esperas dominantes desde la última vez que se restablecieron las estadísticas o desde que se inició el motor de SQL Server.

Después, usa sys.dm_os_waiting_tasks cuando necesites saber qué está esperando en este momento.

Código / comandos

1. Mostrar las principales esperas acumuladas

SELECT TOP (30)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CAST(
        wait_time_ms * 1.0
        / NULLIF(waiting_tasks_count, 0)
        AS decimal(18,2)
    ) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE
    wait_time_ms > 0
ORDER BY
    wait_time_ms DESC;

Usa esta consulta como punto de partida general a nivel de instancia.

Recuerda:

  • wait_time_ms incluye el tiempo de espera de señal
  • signal_wait_time_ms es el tiempo que transcurre desde que un worker recibe la señal hasta que realmente obtiene CPU
  • el tiempo de espera de recursos es aproximadamente:
wait_time_ms - signal_wait_time_ms

2. Excluir las esperas inactivas y en segundo plano habituales para el diagnóstico

Algunas esperas son esperables y pueden dominar los totales sin indicar un cuello de botella que afecte a los usuarios.

Usa una vista filtrada para un diagnóstico práctico.

SELECT TOP (30)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE
    wait_type NOT IN
    (
        N'BROKER_EVENTHANDLER',
        N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',
        N'BROKER_TO_FLUSH',
        N'CHECKPOINT_QUEUE',
        N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',
        N'DBMIRROR_DBM_EVENT',
        N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',
        N'DIRTY_PAGE_POLL',
        N'DISPATCHER_QUEUE_SEMAPHORE',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'HADR_CLUSAPI_CALL',
        N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',
        N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',
        N'HADR_WORK_QUEUE',
        N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',
        N'ONDEMAND_TASK_QUEUE',
        N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',
        N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',
        N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',
        N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',
        N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',
        N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',
        N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP',
        N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'WAITFOR',
        N'WAITFOR_TASKSHUTDOWN',
        N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',
        N'XE_TIMER_EVENT'
    )
    AND wait_time_ms > 0
ORDER BY
    wait_time_ms DESC;

No trates ninguna «lista de exclusión» como universal.

Las exclusiones correctas dependen de:

  • la versión de SQL Server
  • las características habilitadas
  • los Availability Groups
  • Service Broker
  • Query Store
  • la replicación
  • el tipo de carga de trabajo

Usa el filtrado para reducir el ruido, no para ocultar esperas que no conoces.


3. Mostrar qué está esperando en este momento

sys.dm_os_wait_stats es historial acumulado.

Para las esperas actuales, usa:

SELECT
    wt.session_id,
    wt.exec_context_id,
    wt.wait_duration_ms,
    wt.wait_type,
    wt.blocking_session_id,
    wt.resource_description
FROM sys.dm_os_waiting_tasks AS wt
ORDER BY
    wt.wait_duration_ms DESC;

Esta es la consulta adecuada cuando el problema de rendimiento está ocurriendo en este momento.


4. Relacionar las esperas actuales con las solicitudes y el texto SQL

SELECT
    wt.session_id,
    wt.wait_duration_ms,
    wt.wait_type,
    wt.blocking_session_id,
    wt.resource_description,

    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.cpu_time,
    r.logical_reads,

    txt.text AS batch_text
FROM sys.dm_os_waiting_tasks AS wt
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = wt.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
ORDER BY
    wt.wait_duration_ms DESC;

Esto convierte un tipo de espera en algo accionable, porque muestra:

  • qué sesión está esperando
  • qué SQL está ejecutando
  • si otra sesión la está bloqueando
  • qué base de datos está implicada

5. Mostrar las estadísticas de espera de una sesión

Para el historial a nivel de sesión:

DECLARE @session_id int = 57;

SELECT
    session_id,
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_exec_session_wait_stats
WHERE
    session_id = @session_id
ORDER BY
    wait_time_ms DESC;

Esto es útil cuando investigas una sesión concreta y quieres evitar el ruido de toda la instancia.

Las estadísticas de espera de sesión se restablecen cuando la sesión se crea o se restablece, incluido el restablecimiento que realiza el pool de conexiones.


6. Comprobar la hora de inicio de SQL Server

Las estadísticas de espera solo se acumulan desde el último restablecimiento o inicio.

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

Acompaña siempre las estadísticas de espera de su ventana de observación.

Un total de 10 horas de espera en 30 días significa algo muy distinto de 10 horas en 20 minutos.


7. Capturar una línea base sin borrar las esperas

El patrón más seguro en producción es tomar instantáneas de las estadísticas de espera y comparar los deltas.

Ejemplo de primera instantánea:

SELECT
    SYSDATETIMEOFFSET() AS captured_at,
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats;

Guarda ese resultado en una tabla de monitoreo o en un almacén de telemetría externo.

Después, compara dos instantáneas:

delta_wait_time_ms
= current.wait_time_ms - previous.wait_time_ms

delta_wait_count
= current.waiting_tasks_count - previous.waiting_tasks_count

El análisis de deltas es mucho más útil que depender de los totales acumulados desde el inicio.


8. Restablecer las estadísticas de espera solo cuando tengas un motivo deliberado

SQL Server admite:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

No lo uses a la ligera en sistemas de producción.

Borrar las estadísticas de espera elimina la línea base acumulada existente para todas las personas que estén diagnosticando la instancia.

Prefiere el monitoreo mediante instantáneas y deltas.

Qué significa el resultado

Campo Por qué importa
wait_type Nombre del recurso o evento que esperaron los workers de SQL Server.
waiting_tasks_count Número de esperas registradas para ese tipo de espera.
wait_time_ms Tiempo total de espera, incluido el tiempo de espera de señal.
signal_wait_time_ms Tiempo en estado ejecutable después de recibir la señal, pero antes de obtener CPU.
resource_wait_time_ms Aproximación del tiempo de espera sin señal: total menos señal.
avg_wait_ms Duración media aproximada de la espera. Útil como contexto, no como veredicto por sí sola.
blocking_session_id Sesión que bloquea la tarea en espera, cuando corresponde.
resource_description Detalles adicionales sobre el recurso que se está esperando.

Qué revisar

Tiempo de espera de señal alto

Unas esperas de señal altas pueden indicar presión sobre el programador de CPU.

Posibles comprobaciones siguientes:

  • la utilización de CPU del servidor
  • el número de tareas ejecutables
  • sys.dm_os_schedulers
  • las consultas activas costosas
  • la concurrencia
  • las cargas de trabajo paralelas

No diagnostiques presión de CPU solo a partir de las esperas de señal. Correlaciónalas con evidencia real de CPU y del programador.

LCK_M_*

Son esperas de bloqueo.

Siguiente paso:

  • identificar las sesiones bloqueadas
  • identificar el bloqueador principal
  • revisar las transacciones abiertas
  • revisar el alcance de las transacciones y el orden de acceso

No trates por defecto una espera de bloqueo como un problema de indexación.

PAGEIOLATCH_*

Estas esperas indican que hay páginas esperando a leerse desde el almacenamiento a la memoria.

Áreas que conviene investigar:

  • la latencia del almacenamiento
  • la presión sobre la caché de búferes
  • los recorridos (scans) grandes
  • rutas de acceso deficientes
  • una indexación útil insuficiente
  • el crecimiento de la carga de trabajo

No concluyas que «el disco es lento» sin correlacionarlo con la latencia de E/S a nivel de archivo y el comportamiento de las consultas.

PAGELATCH_*

Son esperas de latch en memoria, no esperas normales de E/S en archivos de datos.

Pueden apuntar a contención en estructuras o páginas internas en memoria.

Por ejemplo, la contención de asignación en tempdb puede mostrar ciertos patrones de latch de página.

No confundas PAGELATCH_* con PAGEIOLATCH_*.

SOS_SCHEDULER_YIELD

Indica que los workers cedieron voluntariamente el programador después de consumir su quantum de CPU.

Puede ser normal en sistemas con mucha actividad de CPU.

Si domina durante un periodo de mal rendimiento, investiga las consultas intensivas en CPU y la presión sobre el programador.

CXPACKET / CXCONSUMER

Son esperas relacionadas con el paralelismo.

No significan automáticamente que MAXDOP esté mal configurado.

Investiga:

  • planes paralelos desequilibrados
  • la forma de la consulta
  • las estimaciones de cardinalidad
  • la presión de CPU
  • los operadores de intercambio (exchange)
  • la concurrencia de la carga de trabajo

Las versiones modernas de SQL Server separan en CXCONSUMER más esperas benignas del lado del consumidor, lo que puede hacer que CXPACKET sea más accionable que en versiones anteriores.

WRITELOG

Significa que las sesiones están esperando a que se vacíe el registro de transacciones.

Investiga:

  • la latencia del almacenamiento del registro
  • el tamaño de las transacciones
  • la frecuencia de los commits
  • las cargas de trabajo con mucha escritura
  • los efectos de la replicación síncrona
  • el rendimiento del registro

ASYNC_NETWORK_IO

SQL Server está esperando a que el cliente consuma las filas del resultado.

Posibles causas:

  • un procesamiento lento en el cliente
  • conjuntos de resultados grandes
  • retardo de red
  • consumo fila a fila por parte de la aplicación

No culpes a la red de inmediato.

RESOURCE_SEMAPHORE

Las consultas están esperando concesiones de memoria de ejecución (memory grants).

Investiga:

  • las concesiones de memoria grandes
  • las consultas analíticas concurrentes
  • las malas estimaciones de cardinalidad
  • la forma del plan de consulta
  • la presión sobre la memoria de trabajo (workspace memory)

THREADPOOL

SQL Server está esperando hilos de trabajo (worker threads).

Es una señal de concurrencia de alta prioridad.

Posibles factores:

  • bloqueos graves
  • demasiadas solicitudes concurrentes
  • consultas de larga duración
  • un pool de workers agotado

No «arregles» las esperas de THREADPOOL cambiando max worker threads sin entender la presión de la carga de trabajo.

HADR_*

Las esperas de Availability Groups requieren contexto.

Muchas esperas HADR son esperas en segundo plano esperables.

Si lo que preocupa es el rendimiento de la disponibilidad o de la replicación, analiza la espera HADR concreta junto con las métricas de cola, redo y envío del AG.

Notas de producción

Las estadísticas de espera muestran síntomas, no causas raíz automáticas

Un tipo de espera te dice en qué se está yendo el tiempo.

No siempre te dice por qué.

Ejemplo:

PAGEIOLATCH_SH puede deberse a:

  • un almacenamiento lento
  • un recorrido masivo
  • presión de memoria
  • un índice faltante o ineficiente
  • una regresión de consulta

La corrección adecuada depende de la evidencia que la rodea.

sys.dm_os_wait_stats muestra esperas completadas

Es acumulativa y no representa solo el trabajo que está esperando en este momento.

Para las tareas que están esperando ahora, usa:

sys.dm_os_waiting_tasks

Las estadísticas de espera se restablecen al reiniciar o al borrarlas explícitamente

sys.dm_os_wait_stats no es telemetría histórica duradera.

Haz seguimiento de:

sys.dm_os_sys_info.sqlserver_start_time

y, preferiblemente, toma instantáneas de las estadísticas de espera a lo largo del tiempo.

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

En SQL Server 2019 y versiones anteriores, el acceso amplio a las DMV de espera suele requerir:

VIEW SERVER STATE

En SQL Server 2022 y versiones posteriores:

VIEW SERVER PERFORMANCE STATE

Azure SQL Database y otras plataformas SQL tienen reglas de alcance y permisos diferentes.

Las esperas en segundo plano varían según el entorno

Una lista de esperas inactivas de un servidor puede ser engañosa en otro.

Características como:

  • Availability Groups
  • Service Broker
  • Query Store
  • la replicación
  • los scripts externos

introducen esperas que pueden ser esperables.

Los totales de espera necesitan una ventana de tiempo

Nunca informes:

«Tenemos 3 horas de esperas PAGEIOLATCH»

sin decir también:

«¿en qué periodo?»

La tasa y el delta importan más que los totales desde el inicio.

Errores comunes

1. Tratar la espera principal como la causa raíz

La espera acumulada más alta puede reflejar simplemente la característica de mayor duración de la instancia.

Usa:

  • deltas en una ventana de tiempo
  • esperas actuales
  • evidencia de las consultas
  • métricas del servidor

antes de decidir.

2. Borrar las estadísticas de espera antes de capturarlas

Una vez borradas, la línea base desaparece.

Captura primero.

3. Usar una enorme lista de exclusión universal

El comportamiento de las esperas depende de la versión y de las características.

Filtra el ruido evidente en segundo plano, pero entiende lo que eliminas.

4. Confundir PAGEIOLATCH_* y PAGELATCH_*

Representan clases de problemas distintas.

  • PAGEIOLATCH_* → espera de E/S de página
  • PAGELATCH_* → contención de latch en memoria

5. Suponer que las esperas de paralelismo significan «poner MAXDOP en 1»

El paralelismo puede ser beneficioso.

Investiga los planes paralelos reales, la presión de CPU y el desequilibrio antes de cambiar la configuración a nivel de servidor.

6. Ignorar las esperas de señal

El tiempo de espera de señal puede ayudar a revelar presión en la programación de CPU.

Pero debe correlacionarse con los datos del programador y del CPU.

7. Mirar solo las esperas de toda la instancia

Una sola carga de trabajo ruidosa puede dominar los totales de la instancia.

Usa:

  • las esperas de sesión
  • las tareas que están esperando ahora
  • Query Store
  • el diagnóstico a nivel de solicitud

para acotar el alcance.

8. Comparar entornos sin normalizar la carga de trabajo y el tiempo

Los totales de espera de producción y de pruebas no son directamente comparables cuando:

  • el volumen de carga de trabajo es distinto
  • el tiempo de actividad es distinto
  • el hardware es distinto
  • las características son distintas

Compara tasas y contexto, no totales brutos.

Siguiente paso

Usa el patrón de espera dominante para elegir el siguiente diagnóstico.

  • LCK_M_* → revisa los bloqueos, los bloqueadores principales y las transacciones abiertas.
  • Tiempo de espera de señal alto / SOS_SCHEDULER_YIELD → revisa la presión sobre el CPU y el programador.
  • PAGEIOLATCH_* → revisa la latencia de E/S de los archivos, la presión de memoria, los recorridos y las rutas de acceso de las consultas.
  • PAGELATCH_* → revisa la contención de latch en memoria, incluidos los patrones de tempdb cuando corresponda.
  • WRITELOG → revisa la latencia del registro de transacciones, el tamaño de las transacciones y los patrones de commit.
  • ASYNC_NETWORK_IO → revisa el tamaño del conjunto de resultados, el consumo en el cliente y el comportamiento de la red.
  • RESOURCE_SEMAPHORE → revisa las concesiones de memoria y la cardinalidad de las consultas.
  • THREADPOOL → revisa con urgencia la concurrencia, los bloqueos y el agotamiento de workers.
  • Esperas de paralelismo → revisa el desequilibrio de los planes, la presión de CPU y la carga de trabajo antes de cambiar MAXDOP.
  • Nada claramente anómalo → compara los deltas de espera en una ventana de tiempo conocida y continúa con Query Store y el análisis de las solicitudes activas.

← Todas las guías rápidas