sys.dm_os_waiting_tasks - Usage & Examples

When SQL Server tasks cannot proceed due to resource contention, they enter a wait state and register in sys.dm_os_waiting_tasks. The SQLOS scheduler places these tasks in wait queues managed by the wait manager. Each waiting task records its wait_type, blocking_task_address, and resource_address.

The DMV exposes the internal TASK structure's wait-related fields. When a task calls SOS_Task::Wait(), it populates the wait_type field with one of 800+ possible wait types. The blocking_task_address links to the sys.dm_os_tasks entry of the blocking session, while resource_address points to the specific resource being waited for.

SQL Server 2016 introduced sys.dm_exec_session_wait_stats which aggregates historical waits per session, but sys.dm_os_waiting_tasks remains essential for real-time blocking analysis. SQL Server 2019 added wait_duration_ms improvements for more precise timing. SQL Server 2022 enhanced the DMV's performance under high concurrency by optimizing the underlying hash table lookups.

The scheduler's quantum expiry mechanism interacts with this DMV when THREADPOOL waits occur. CPU pressure forces tasks to yield and wait for available worker threads. Memory pressure manifests as RESOURCE_SEMAPHORE waits when queries cannot obtain sufficient memory grants.

-- Current blocking chains with wait duration
SELECT 
    wt.session_id,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.blocking_session_id,
    wt.resource_description,
    s.program_name,
    s.login_name,
    r.command,
    r.sql_handle
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_exec_sessions s ON wt.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON wt.session_id = r.session_id
WHERE wt.session_id > 50
ORDER BY wt.wait_duration_ms DESC;

-- Wait type analysis with blocking task details
SELECT 
    wt.wait_type,
    COUNT(*) as wait_count,
    AVG(wt.wait_duration_ms) as avg_wait_ms,
    MAX(wt.wait_duration_ms) as max_wait_ms,
    STRING_AGG(CAST(wt.session_id AS VARCHAR), ', ') as waiting_sessions
FROM sys.dm_os_waiting_tasks wt
WHERE wt.session_id > 50
GROUP BY wt.wait_type
HAVING COUNT(*) > 1
ORDER BY avg_wait_ms DESC;

-- Lock-specific waits with resource details
SELECT 
    wt.session_id,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.blocking_session_id,
    wt.resource_description,
    tl.resource_type,
    tl.resource_database_id,
    tl.resource_associated_entity_id,
    DB_NAME(tl.resource_database_id) as database_name
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_tran_locks tl ON wt.resource_address = tl.lock_owner_address
WHERE wt.wait_type LIKE 'LCK_%'
ORDER BY wt.wait_duration_ms DESC;

-- Memory grant waits with query details
SELECT 
    wt.session_id,
    wt.wait_duration_ms,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    mg.query_cost,
    mg.dop,
    st.text as query_text
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_exec_query_memory_grants mg ON wt.session_id = mg.session_id
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) st
WHERE wt.wait_type = 'RESOURCE_SEMAPHORE'
ORDER BY wt.wait_duration_ms DESC;

-- I/O related waits with file details
SELECT 
    wt.session_id,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.resource_description,
    r.reads,
    r.writes,
    r.logical_reads,
    DB_NAME(r.database_id) as database_name
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_exec_requests r ON wt.session_id = r.session_id
WHERE wt.wait_type IN ('PAGEIOLATCH_SH', 'PAGEIOLATCH_EX', 'WRITELOG', 'LOGMGR_QUEUE')
ORDER BY wt.wait_duration_ms DESC;

Terminate blocking sessions causing excessive waits

-- Identify and optionally kill long-running blockers
-- WARNING: Test impact before using KILL in production
DECLARE @kill_threshold_ms INT = 30000; -- 30 seconds
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 'KILL ' + CAST(blocking_session_id AS VARCHAR) + '; '
FROM (
    SELECT DISTINCT wt.blocking_session_id,
           COUNT(*) as blocked_sessions,
           MAX(wt.wait_duration_ms) as max_wait_ms
    FROM sys.dm_os_waiting_tasks wt
    WHERE wt.blocking_session_id IS NOT NULL
      AND wt.blocking_session_id > 50
      AND wt.wait_duration_ms > @kill_threshold_ms
    GROUP BY wt.blocking_session_id
    HAVING COUNT(*) > 3  -- Blocking more than 3 sessions
) blockers;

-- Review before executing
PRINT @sql;
-- EXEC sp_executesql @sql; -- Uncomment after review

Clear memory grant queue backlog

-- Force memory grant recalculation for stuck queries
-- Reduces RESOURCE_SEMAPHORE waits
DECLARE @session_id INT;
DECLARE session_cursor CURSOR FOR
    SELECT DISTINCT wt.session_id
    FROM sys.dm_os_waiting_tasks wt
    WHERE wt.wait_type = 'RESOURCE_SEMAPHORE'
      AND wt.wait_duration_ms > 60000; -- 1 minute

OPEN session_cursor;
FETCH NEXT FROM session_cursor INTO @session_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Cancel the query to free memory grant
    DECLARE @kill_cmd NVARCHAR(50) = 'KILL ' + CAST(@session_id AS VARCHAR);
    PRINT 'Cancelling session: ' + CAST(@session_id AS VARCHAR);
    -- EXEC sp_executesql @kill_cmd; -- Uncomment after validation
    
    FETCH NEXT FROM session_cursor INTO @session_id;
END;

CLOSE session_cursor;
DEALLOCATE session_cursor;

Enable Query Store for wait statistics collection

-- Configure Query Store to capture wait statistics
-- Helps with historical analysis of wait patterns
USE [YourDatabase]; -- Replace with actual database name

ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO,
    WAIT_STATS_CAPTURE_MODE = ON  -- SQL Server 2017+
);

Create blocking monitoring alert

-- Create SQL Agent alert for excessive blocking
-- Triggers when blocking persists beyond threshold
USE msdb;
GO

EXEC sp_add_alert
    @name = N'Blocking_Chains_Alert',
    @message_id = 1205, -- Deadlock message
    @severity = 0,
    @performance_condition = N'SQLServer:General Statistics|Processes blocked||>|5',
    @job_name = N'Investigate_Blocking'; -- Create this job separately

-- Alternative: Custom WMI alert for waiting tasks
EXEC sp_add_alert
    @name = N'Long_Wait_Tasks_Alert',
    @wmi_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
    @wmi_query = N'SELECT * FROM BLOCKED_PROCESS_REPORT',
    @job_name = N'Log_Blocking_Details';

Configure blocked process monitoring with sp_configure 'blocked process threshold' set to 5-10 seconds for early blocking detection. Enable Query Store wait statistics capture in SQL Server 2017+ to identify patterns causing waits.

Implement proper indexing strategies to reduce LCK_M_* waits. Use covering indexes for frequently accessed columns and partition large tables to minimize lock scope. Enable READ_COMMITTED_SNAPSHOT isolation where appropriate to reduce reader-writer blocking.

Configure Resource Governor to prevent runaway queries from consuming excessive memory grants. Set appropriate MAXDOP values (typically CPU cores divided by 2) and cost threshold for parallelism (start with 50) to reduce CXPACKET and parallelism-related waits.

Monitor sys.dm_os_wait_stats regularly to identify trending wait types before they cause sys.dm_os_waiting_tasks accumulation. Establish baselines for normal wait patterns and alert on deviations exceeding 2-3 standard deviations.

Use connection pooling and implement retry logic in applications to handle transient blocking. Avoid long-running transactions during peak hours and implement proper transaction scope management to minimize lock hold duration.