sys.dm_exec_requests (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Returns information about each request that is executing in SQL Server. For more information about requests, see the Thread and Task Architecture Guide.

To execute code that is outside SQL Server (for example, extended stored procedures and distributed queries), a thread has to execute outside the control of the non-preemptive scheduler. To do this, a worker switches to preemptive mode. Time values returned by this dynamic management view don't include time spent in preemptive mode.

When executing parallel requests in row mode, SQL Server assigns a worker thread to coordinate the worker threads responsible for completing tasks assigned to them. In this DMV, only the coordinator thread is visible for the request. The columns reads, writes, logical_reads, and row_count are not updated for the coordinator thread. The columns wait_type, wait_time, last_wait_type, wait_resource, and granted_query_memory are only updated for the coordinator thread. For more information, see the Thread and Task Architecture Guide.

If the user has VIEW SERVER STATE permission on the server, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session. VIEW SERVER STATE can't be granted in Azure SQL Database so sys.dm_exec_requests is always limited to the current connection.

In availability group scenarios, if the secondary replica is set to read-intent only, the connection to the secondary must specify its application intent in connection string parameters by adding applicationintent=readonly. Otherwise, the access check for sys.dm_exec_requests won't pass for databases in the availability group, even if VIEW SERVER STATE permission is present.

For SQL Server 2022 (16.x) and later versions, sys.dm_exec_requests requires VIEW SERVER PERFORMANCE STATE permission on the server.

The following example queries sys.dm_exec_requests to find the interesting query and copy its sql_handle from the output.

SELECT * FROM sys.dm_exec_requests;
GO

Then, to obtain the statement text, use the copied sql_handle with system function sys.dm_exec_sql_text(sql_handle).

SELECT * FROM sys.dm_exec_sql_text(< copied sql_handle >);
GO

The following example queries sys.dm_exec_requests to find the interesting batch and copy its transaction_id from the output.

SELECT * FROM sys.dm_exec_requests;
GO

Then, to find lock information, use the copied transaction_id with the system function sys.dm_tran_locks.

SELECT * FROM sys.dm_tran_locks
WHERE request_owner_type = N'TRANSACTION'
    AND request_owner_id = < copied transaction_id >;
GO

The following example queries sys.dm_exec_requests to find information about blocked requests.

SELECT session_id,
    status,
    blocking_session_id,
    wait_type,
    wait_time,
    wait_resource,
    transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended';
GO

SELECT
    [req].[session_id],
    [req].[start_time],
    [req].[cpu_time] AS [cpu_time_ms],
    OBJECT_NAME([ST].[objectid], [ST].[dbid]) AS [ObjectName],
    SUBSTRING(
        REPLACE(
            REPLACE(
                SUBSTRING(
                    [ST].[text], ([req].[statement_start_offset] / 2) + 1,
                    ((CASE [req].[statement_end_offset]
                            WHEN -1 THEN DATALENGTH([ST].[text])
                            ELSE [req].[statement_end_offset]
                        END - [req].[statement_start_offset]
                        ) / 2
                    ) + 1
                ), CHAR(10), ' '
            ), CHAR(13), ' '
        ), 1, 512
    ) AS [statement_text]
FROM
    [sys].[dm_exec_requests] AS [req]
    CROSS APPLY [sys].dm_exec_sql_text([req].[sql_handle]) AS [ST]
ORDER BY
    [req].[cpu_time] DESC;
GO

• System Dynamic Management Views
• Execution Related Dynamic Management Views and Functions
• sys.dm_os_memory_clerks
• sys.dm_os_sys_info
• sys.dm_exec_query_memory_grants
• sys.dm_exec_query_plan
• sys.dm_exec_sql_text
• SQL Server, SQL Statistics object
• Query Processing Architecture Guide
• Thread and Task Architecture Guide

• Understand and resolve SQL Server blocking problems