View waits for active requests

A request is suspended, but the wait type displayed in its queue does not account for all of the tasks involved. This is especially true in parallel queries, where different tasks may wait for different reasons.

What we need to distinguish

A task is one unit of work inside a request. Parallelism lets several tasks cooperate on one query, so each may show a different wait.

A request can split its work into multiple tasks, especially when it uses parallelism. Each task can be running or waiting for a different resource; that’s why a single session can produce multiple rows during the same capture.

The wait type classifies what temporarily stops the task. The resource adds the available detail and the blocking session identifies another connection only when the wait corresponds to blocking. None of that isolated data yet establishes the root cause.

Query

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Consulta
SELECT
    wt.session_id AS IdSesion,
    wt.exec_context_id AS IdContextoEjecucion,
    wt.wait_duration_ms AS EsperaMilisegundos,
    wt.wait_type AS TipoEspera,
    wt.blocking_session_id AS IdSesionBloqueadora,
    wt.resource_description AS Recurso,
    s.login_name AS InicioSesion,
    s.host_name AS EquipoCliente,
    s.program_name AS Aplicacion,
    solicitud.request_id AS IdSolicitudRelacionada,
    solicitud.status AS EstadoSolicitud,
    solicitud.command AS Comando,
    solicitud.cpu_time AS CpuMilisegundos,
    solicitud.total_elapsed_time AS DuracionMilisegundos,
    texto.text AS LoteRelacionado
FROM sys.dm_os_waiting_tasks AS wt
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = wt.session_id
OUTER APPLY
(
    SELECT TOP (1)
        r.request_id,
        r.status,
        r.command,
        r.cpu_time,
        r.total_elapsed_time,
        r.sql_handle,
        r.task_address,
        r.start_time
    FROM sys.dm_exec_requests AS r
    WHERE r.session_id = wt.session_id
    ORDER BY
        CASE
            WHEN r.task_address = wt.waiting_task_address THEN 0
            ELSE 1
        END,
        r.start_time DESC,
        r.request_id
) AS solicitud
OUTER APPLY sys.dm_exec_sql_text
(
    solicitud.sql_handle
) AS texto
WHERE s.is_user_process = 1
  AND wt.session_id <> @@SPID
ORDER BY
    wt.wait_duration_ms DESC,
    wt.session_id,
    wt.exec_context_id;
GO

Parameters or permissions

The query uses dynamic management views (DMVs), which expose the changing state of the server, and requires the appropriate permissions: typically VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE since SQL Server 2022. A parallel request can produce multiple queues because each task maintains its own wait. The association with RelatedRequestId attempts to prefer a match by task address and then the most recent request in the session; with Multiple Active Result Sets (MARS) or parallel tasks there is not always an unambiguous relationship.

How to read the result

WaitType names the current category and Resource provides the detail that SQL Server can describe. Multiple tasks in the same session should not be simply added: they can be waiting simultaneously. Short, frequent waiting may be normal. Before treating it as a problem, it is necessary to check the duration, number of sessions affected, consumption and repeated behavior. Lock waits are related to blocking sessions and lock chain; Other families need a different review.

What this does establish

Allows you to observe user tasks waiting during capture and gather session, resource, possible blocker, and related batch.

What this still does not establish

It does not represent server history, does not automatically attribute a root cause, and does not allow parallel task times to be added as if they were consecutive executions.

Next reasonable check

Repeat the capture during the incident and compare what you expect to persist. To find out if a family dominates since the last reset, check accumulated waits; for blocking, see blocking sessions and full chain.

Search SQL Server