Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

sys.dm_exec_requests (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns information about each request that is executing within SQL Server.

System_CAPS_ICON_note.jpg Note


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 do not include time spent in preemptive mode.

System_CAPS_ICON_note.jpg Note


To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_exec_requests.

Column nameData typeDescription
session_idsmallintID of the session to which this request is related. Is not nullable.
request_idintID of the request. Unique in the context of the session. Is not nullable.
start_timedatetimeTimestamp when the request arrived. Is not nullable.
statusnvarchar(30)Status of the request. This can be one of the following:

Background
Running
Runnable
Sleeping
Suspended

Is not nullable.
commandnvarchar(32)Identifies the current type of command that is being processed. Common command types include the following:

SELECT
INSERT
UPDATE
DELETE
BACKUP LOG
BACKUP DATABASE
DBCC
FOR

The text of the request can be retrieved by using sys.dm_exec_sql_text with the corresponding sql_handle for the request. Internal system processes set the command based on the type of task they perform. Tasks can include the following:

LOCK MONITOR
CHECKPOINTLAZY
WRITER

Is not nullable.
sql_handlevarbinary(64)Hash map of the SQL text of the request. Is nullable.
statement_start_offsetintNumber of characters into the currently executing batch or stored procedure at which the currently executing statement starts. Can be used together with the sql_handle, the statement_end_offset, and the sys.dm_exec_sql_text dynamic management function to retrieve the currently executing statement for the request. Is nullable.
statement_end_offsetintNumber of characters into the currently executing batch or stored procedure at which the currently executing statement ends. Can be used together with the sql_handle, the statement_end_offset, and the sys.dm_exec_sql_text dynamic management function to retrieve the currently executing statement for the request. Is nullable.
plan_handlevarbinary(64)Hash map of the plan for SQL execution. Is nullable.
database_idsmallintID of the database the request is executing against. Is not nullable.
user_idintID of the user who submitted the request. Is not nullable.
connection_iduniqueidentifierID of the connection on which the request arrived. Is nullable.
blocking_session_idsmallintID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).

-2 = The blocking resource is owned by an orphaned distributed transaction.

-3 = The blocking resource is owned by a deferred recovery transaction.

-4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.
wait_typenvarchar(60)If the request is currently blocked, this column returns the type of wait. Is nullable.

For information about types of waits, see sys.dm_os_wait_stats (Transact-SQL).
wait_timeintIf the request is currently blocked, this column returns the duration in milliseconds, of the current wait. Is not nullable.
last_wait_typenvarchar(60)If this request has previously been blocked, this column returns the type of the last wait. Is not nullable.
wait_resourcenvarchar(256)If the request is currently blocked, this column returns the resource for which the request is currently waiting. Is not nullable.
open_transaction_countintNumber of transactions that are open for this request. Is not nullable.
open_resultset_countintNumber of result sets that are open for this request. Is not nullable.
transaction_idbigintID of the transaction in which this request executes. Is not nullable.
context_infovarbinary(128)CONTEXT_INFO value of the session. Is nullable.
percent_completerealPercentage of work completed for the following commands:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

Is not nullable.
estimated_completion_timebigintInternal only. Is not nullable.
cpu_timeintCPU time in milliseconds that is used by the request. Is not nullable.
total_elapsed_timeintTotal time elapsed in milliseconds since the request arrived. Is not nullable.
scheduler_idintID of the scheduler that is scheduling this request. Is not nullable.
task_addressvarbinary(8)Memory address allocated to the task that is associated with this request. Is nullable.
readsbigintNumber of reads performed by this request. Is not nullable.
writesbigintNumber of writes performed by this request. Is not nullable.
logical_readsbigintNumber of logical reads that have been performed by the request. Is not nullable.
text_sizeintTEXTSIZE setting for this request. Is not nullable.
languagenvarchar(128)Language setting for the request. Is nullable.
date_formatnvarchar(3)DATEFORMAT setting for the request. Is nullable.
date_firstsmallintDATEFIRST setting for the request. Is not nullable.
quoted_identifierbit1 = QUOTED_IDENTIFIER is ON for the request. Otherwise, it is 0.

Is not nullable.
arithabortbit1 = ARITHABORT setting is ON for the request. Otherwise, it is 0.

Is not nullable.
ansi_null_dflt_onbit1 = ANSI_NULL_DFLT_ON setting is ON for the request. Otherwise, it is 0.

Is not nullable.
ansi_defaultsbit1 = ANSI_DEFAULTS setting is ON for the request. Otherwise, it is 0.

Is not nullable.
ansi_warningsbit1 = ANSI_WARNINGS setting is ON for the request. Otherwise, it is 0.

Is not nullable.
ansi_paddingbit1 = ANSI_PADDING setting is ON for the request.

Otherwise, it is 0.

Is not nullable.
ansi_nullsbit1 = ANSI_NULLS setting is ON for the request. Otherwise, it is 0.

Is not nullable.
concat_null_yields_nullbit1 = CONCAT_NULL_YIELDS_NULL setting is ON for the request. Otherwise, it is 0.

Is not nullable.
transaction_isolation_levelsmallintIsolation level with which the transaction for this request is created. Is not nullable.

0 = Unspecified

1 = ReadUncomitted

2 = ReadCommitted

3 = Repeatable

4 = Serializable

5 = Snapshot
lock_timeoutintLock time-out period in milliseconds for this request. Is not nullable.
deadlock_priorityintDEADLOCK_PRIORITY setting for the request. Is not nullable.
row_countbigintNumber of rows that have been returned to the client by this request. Is not nullable.
prev_errorintLast error that occurred during the execution of the request. Is not nullable.
nest_levelintCurrent nesting level of code that is executing on the request. Is not nullable.
granted_query_memoryintNumber of pages allocated to the execution of a query on the request. Is not nullable.
executing_managed_codebitIndicates whether a specific request is currently executing common language runtime objects, such as routines, types, and triggers. It is set for the full time a common language runtime object is on the stack, even while running Transact-SQL from within common language runtime. Is not nullable.
group_idintID of the workload group to which this query belongs. Is not nullable.
query_hashbinary(8)Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.
query_plan_hashbinary(8)Binary hash value calculated on the query execution plan and used to identify similar query execution plans. You can use query plan hash to find the cumulative cost of queries with similar execution plans.
statement_sql_handlevarbinary(64)Applies to: SQL Server 2014 through SQL Server 2016.

Reserved for future use.
statement_context_idbigintApplies to: SQL Server 2014 through SQL Server 2016.

Reserved for future use.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Requires VIEW SERVER STATE permission on the server.

System_CAPS_ICON_note.jpg Note


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.

A. Finding the query text for a running batch

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  

B. Finding all locks that a running batch is holding

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  

C. Finding all currently blocked requests

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  

Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_os_memory_clerks (Transact-SQL)
sys.dm_os_sys_info (Transact-SQL)
sys.dm_exec_query_memory_grants (Transact-SQL)
sys.dm_exec_query_plan (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)

Community Additions

Show: