sys.dm_exec_requests - Usage & Examples

sys.dm_exec_requests operates at the SQLOS scheduler level, capturing request state transitions as they move through SQL Server's execution pipeline. When a session executes a query, SQL Server creates a request context that tracks the execution from parse through completion. The DMV exposes this internal request tracking structure in real-time.

The request lifecycle begins when SQL Server receives a batch from a client connection. The query processor assigns the request to a worker thread on a SQLOS scheduler, creating entries in sys.dm_exec_requests with status 'running'. When requests encounter resource waits (locks, I/O, memory), the status changes to 'suspended' and populates wait_type and wait_resource columns. The SQLOS scheduler maintains these state transitions until request completion.

Critical columns reflect internal mechanisms: scheduler_id maps to the SQLOS scheduler handling the request, task_address links to sys.dm_os_tasks for thread-level details, and sql_handle connects to the plan cache via sys.dm_exec_sql_text. The blocking_session_id column emerges from the lock manager's dependency tracking, while wait_time accumulates from the wait statistics subsystem.

SQL Server 2016 introduced live query statistics integration, adding more detailed execution metrics. SQL Server 2019 enhanced memory grant tracking with additional columns for resource governor classification. SQL Server 2022 expanded the DMV with Intelligent Query Processing metrics and parameter-sensitive plan optimization indicators.

The row_count and estimated_completion_time columns derive from the query execution engine's cardinality estimation and progress tracking. These values update dynamically as the query processor refines estimates during execution, particularly for large table scans and index operations.

-- Identify currently blocking requests and their wait chains
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_resource,
    r.wait_time,
    DB_NAME(r.database_id) AS database_name,
    r.cpu_time,
    r.logical_reads,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1,
        CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
             ELSE r.statement_end_offset END - r.statement_start_offset)/2 + 1) AS statement_text
FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50
    AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

-- Find high resource consumption queries currently executing
SELECT TOP 10
    r.session_id,
    r.command,
    r.cpu_time,
    r.logical_reads,
    r.writes,
    r.total_elapsed_time,
    r.granted_query_memory * 8 AS granted_memory_kb,
    r.dop AS degree_of_parallelism,
    CAST(r.percent_complete AS decimal(5,2)) AS percent_complete,
    st.text AS full_text
FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50
ORDER BY r.cpu_time + r.logical_reads DESC;

-- Analyze wait patterns for suspended requests
SELECT 
    wait_type,
    COUNT(*) as request_count,
    AVG(wait_time) as avg_wait_ms,
    MAX(wait_time) as max_wait_ms,
    SUM(CASE WHEN wait_time > 10000 THEN 1 ELSE 0 END) as long_waits_10sec_plus
FROM sys.dm_exec_requests
WHERE status = 'suspended'
    AND session_id > 50
GROUP BY wait_type
HAVING COUNT(*) > 1
ORDER BY request_count DESC;

-- Correlate requests with their execution plans and plan cache usage
SELECT 
    r.session_id,
    r.request_id,
    r.plan_handle,
    cp.usecounts,
    cp.size_in_bytes,
    cp.cacheobjtype,
    r.cpu_time,
    r.logical_reads,
    qp.query_plan,
    st.text
FROM sys.dm_exec_requests r
    LEFT JOIN sys.dm_exec_cached_plans cp ON r.plan_handle = cp.plan_handle
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
    CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.session_id > 50
    AND r.sql_handle IS NOT NULL
ORDER BY r.cpu_time DESC;

-- Monitor parallel query execution and MAXDOP effectiveness
SELECT 
    r.session_id,
    r.request_id,
    r.dop,
    COUNT(t.task_address) AS active_tasks,
    AVG(w.worker_time) AS avg_worker_time,
    MAX(w.worker_time) AS max_worker_time,
    r.cpu_time,
    st.text
FROM sys.dm_exec_requests r
    INNER JOIN sys.dm_os_tasks t ON r.session_id = t.session_id AND r.request_id = t.request_id
    INNER JOIN sys.dm_os_workers w ON t.worker_address = w.worker_address
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50
    AND r.dop > 1
GROUP BY r.session_id, r.request_id, r.dop, r.cpu_time, st.text
ORDER BY COUNT(t.task_address) DESC;

Kill Long-Running Blocking Sessions Terminates sessions that have been blocking others for extended periods. Use this when blocking chains prevent normal operations.

-- CAUTION: Test in development first. This will terminate active sessions.
DECLARE @BlockingSessionId INT, @Cmd NVARCHAR(100);
DECLARE blocking_cursor CURSOR FOR
    SELECT DISTINCT blocking_session_id
    FROM sys.dm_exec_requests 
    WHERE blocking_session_id > 0 
        AND wait_time > 300000  -- 5 minutes
        AND blocking_session_id NOT IN (SELECT session_id FROM sys.dm_exec_requests WHERE command = 'BACKUP DATABASE');

OPEN blocking_cursor;
FETCH NEXT FROM blocking_cursor INTO @BlockingSessionId;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Cmd = 'KILL ' + CAST(@BlockingSessionId AS VARCHAR(10));
    PRINT 'Executing: ' + @Cmd;
    -- Uncomment the next line after testing:
    -- EXEC sp_executesql @Cmd;
    FETCH NEXT FROM blocking_cursor INTO @BlockingSessionId;
END;

CLOSE blocking_cursor;
DEALLOCATE blocking_cursor;

Force Query Plan Recompilation for High CPU Queries Clears plan cache entries for queries consuming excessive CPU to force recompilation with current statistics.

-- Forces recompilation of high CPU queries by removing their plans from cache
-- Test impact during low-activity periods
DECLARE @PlanHandle VARBINARY(64), @Cmd NVARCHAR(500);
DECLARE plan_cursor CURSOR FOR
    SELECT DISTINCT r.plan_handle
    FROM sys.dm_exec_requests r
    WHERE r.cpu_time > 30000  -- 30 seconds CPU
        AND r.session_id > 50
        AND r.plan_handle IS NOT NULL;

OPEN plan_cursor;
FETCH NEXT FROM plan_cursor INTO @PlanHandle;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Cmd = 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(130), @PlanHandle, 1) + ')';
        PRINT 'Removing plan: ' + CONVERT(VARCHAR(130), @PlanHandle, 1);
        EXEC sp_executesql @Cmd;
    END TRY
    BEGIN CATCH
        PRINT 'Failed to remove plan: ' + CONVERT(VARCHAR(130), @PlanHandle, 1);
    END CATCH;
    
    FETCH NEXT FROM plan_cursor INTO @PlanHandle;
END;

CLOSE plan_cursor;
DEALLOCATE plan_cursor;

Adjust MAXDOP for Inefficient Parallel Queries Modifies MAXDOP at query level for sessions showing poor parallel execution efficiency.

-- Identifies and suggests MAXDOP adjustments for poorly performing parallel queries
-- This generates recommendations rather than making automatic changes
WITH ParallelEfficiency AS (
    SELECT 
        r.session_id,
        r.dop,
        COUNT(t.task_address) AS actual_tasks,
        r.cpu_time / NULLIF(COUNT(t.task_address), 0) AS cpu_per_task,
        CASE 
            WHEN COUNT(t.task_address) < r.dop * 0.5 THEN 'Reduce MAXDOP'
            WHEN r.cpu_time / NULLIF(COUNT(t.task_address), 0) < 1000 THEN 'Consider Serial'
            ELSE 'Acceptable'
        END AS recommendation
    FROM sys.dm_exec_requests r
        INNER JOIN sys.dm_os_tasks t ON r.session_id = t.session_id 
    WHERE r.dop > 1 
        AND r.session_id > 50
    GROUP BY r.session_id, r.dop, r.cpu_time
)
SELECT 
    session_id,
    dop AS current_maxdop,
    actual_tasks,
    cpu_per_task,
    recommendation,
    CASE 
        WHEN recommendation = 'Reduce MAXDOP' THEN CAST(actual_tasks AS VARCHAR(10))
        WHEN recommendation = 'Consider Serial' THEN '1'
        ELSE CAST(dop AS VARCHAR(10))
    END AS suggested_maxdop
FROM ParallelEfficiency
WHERE recommendation <> 'Acceptable'
ORDER BY cpu_per_task;

Configure cost threshold for parallelism above 50 to prevent small queries from going parallel unnecessarily. Set MAXDOP to number of physical cores per NUMA node, never exceeding 8 for OLTP workloads. Enable Query Store in SQL Server 2016+ to identify plan regressions before they impact production.

Implement regular statistics updates using maintenance plans or custom scripts. Outdated statistics cause poor cardinality estimates, leading to inefficient execution plans that appear in sys.dm_exec_requests as long-running queries. Schedule statistics updates during maintenance windows, not during peak hours.

Monitor sys.dm_exec_requests continuously using custom alerting on wait_time thresholds. Create alerts for requests waiting longer than 30 seconds on PAGEIOLATCH waits (indicating I/O pressure) or LCK waits (indicating blocking). Establish baselines for normal cpu_time and logical_reads values to detect performance degradation early.

Configure Resource Governor to limit runaway queries before they consume all server resources. Create resource pools with MAX_CPU_PERCENT and REQUEST_MAX_CPU_TIME_SEC limits. Apply workload groups to classify different application types and prevent one application from monopolizing server resources.

Use Extended Events sessions targeting long-running queries rather than constantly polling sys.dm_exec_requests. Create sessions that capture sql_statement_completed events with duration filters, reducing monitoring overhead while maintaining visibility into performance issues.