5 out of 5 rated this helpful - Rate this topic

sys.dm_exec_requests

SQL Server 2005

Updated: 12 December 2006

Returns one row for each request executing within SQL Server. The sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests server scope dynamic management views map to the sys.sysprocesses system view (previously system table).

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.

Column name

Data type

Description

session_id

smallint

ID of the session to which this request is related. Is not nullable.

request_id

int

ID of the request. Unique in the context of the session. Is not nullable.

start_time

datetime

Time when the request is scheduled to run. Is not nullable.

status

nvarchar(60)

Status of the request. Possible values are as follows:

Background. The request is a background thread such as Resource Monitor or Deadlock Monitor.
Running. The request is running.
Runnable. The request is running and temporarily scheduled out because it is running out of quorum.
Sleeping. There is no work to be done.
Pending. The request is waiting for a worker to pick it up.
Suspended. The request is waiting for some event.

Is not nullable.

command

nvarchar(32)

Identifies the type of command that is being processed. Common command types include the following:

SELECT
INSERT
UPDATE
DELETE
BACKUP LOG
BACKUP DB
DBCC
WAITFOR

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

LOCK MONITOR
CHECKPOINTLAZY
WRITER

Is not nullable.

sql_handle

varbinary(64)

The handle to the request's SQL statement. This handle can be used to retrieve the actual statement text from the sys.dm_exec_sql_textdynamic management function. Is not nullable.

statement_start_offset

int

Starting character position of the executing statement in the executing batch or stored procedure. Can be used together with the statement_end_offset, the sys.dm_exec_sql_text dynamic management function, and the sql_handle to retrieve the executing statement for the request. Is nullable.

statement_end_offset

int

Ending character position of the executing statement in the executing batch or stored procedure. Can be used together with the statement_start_offset, the sys.dm_exec_sql_text dynamic management function, and the sql_handle to retrieve the executing statement for the request. Is nullable.

plan_handle

varbinary(64)

The handle to the query plan of the request. To see the query plan, use with the sys.dm_exec_query_plan dynamic management function. To query the plan cache, use the sys.dm_exec_cached_plans dynamic management view. To see the plan attributes, use the sys.dm_exec_plan_attributes function. Is nullable.

database_id

smallint

ID of the database the request is running under. For more database information, query the sys.databases catalog view; or to obtain the database name, use the db_name()intrinsic function. Is not nullable.

user_id

int

User ID the request is running under. For more user information, query the sys.database_principals catalog view. Is not nullable.

connection_id

uniqueidentifier

ID of the connection on which the request arrived. For more information about the physical or logical connection, query the sys.dm_exec_connections dynamic management view. Is nullable.

blocking_session_id

smallint

ID of the session that is blocking the request. If this column is 0, the request is not blocked, or information for 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 because of internal latch state transitions.

wait_type

nvarchar(60)

If the request is blocked, this column returns the type of wait. Is nullable.

wait_time

int

If the request is blocked, this column returns the duration in milliseconds, of the current wait. Is not nullable.

last_wait_type

nvarchar(64)

If this request has previously been blocked, this column returns the type of the last wait. Is not nullable.

wait_resource

nvarchar(512)

If the request is blocked, this column returns the resource for which the request is waiting. Is not nullable.

open_transaction_count

int

Number of transactions that are open for this request. Is not nullable.

open_resultset_count

int

Number of result sets that are open for this request. Is not nullable.

transaction_id

bigint

ID of the transaction in which this request executes. This ID is unique for an instance of SQL Server. Use to query the sys.dm_tran_active_transactions, sys.dm_tran_locks, or sys.dm_tran_database_transactions dynamic management views. Is not nullable.

context_info

varbinary(128)

Value from the SET CONTEXT_INFO statement for the request. Is nullable.

percent_complete

real

Percent of work completed for certain operations, rollbacks included.

This does not provide progress data for queries.

Note:

Is not nullable.

estimated_completion_time

bigint

Internal only. Is not nullable.

cpu_time

int

CPU time in milliseconds that is used by the request. Is not nullable.

total_elapsed_time

int

Total time elapsed in milliseconds since the request arrived. Is not nullable.

scheduler_id

int

ID of the scheduler scheduling this request. For more information about this scheduler, query the sys.dm_os_schedulers dynamic management view. Is not nullable.

task_address

varbinary(8)

Memory address allocated to the task that is associated with this request. For more information about this task, query the sys.dm_os_tasks dynamic management view. Is nullable.

reads

bigint

Number of reads performed by this request. Is not nullable.

writes

bigint

Number of writes performed by this request. Is not nullable.

logical_reads

bigint

Number of logical reads that have been performed by the request. Is not nullable.

text_size

int

TEXTSIZE setting for this request. Is not nullable.

language

nvarchar(256)

Language setting for the request. Is nullable.

date_format

nvarchar(3)

DATEFORMAT setting for the request. Is nullable.

date_first

smallint

DATEFIRST setting for the request. Is not nullable.

quoted_identifier

bit

1 = QUOTED_IDENTIFIER is ON for the request. Otherwise, it is 0.

Is not nullable.

arithabort

bit

1 = ARITHABORT setting is ON for the request. Otherwise, it is 0.

Is not nullable.

ansi_null_dflt_on

bit

1 = ANSI_NULL_DFLT_ON setting is ON for the request. Otherwise, it is 0.

Is not nullable.

ansi_defaults

bit

1 = ANSI_DEFAULTS setting is ON for the request. Otherwise, it is 0.

Is not nullable.

ansi_warnings

bit

1 = ANSI_WARNINGS setting is ON for the request. Otherwise, it is 0.

Is not nullable.

ansi_padding

bit

1 = ANSI_PADDING setting is ON for the request.

Otherwise, it is 0.

Is not nullable.

ansi_nulls

bit

1 = ANSI_NULLS setting is ON for the request. Otherwise, it is 0.

Is not nullable.

concat_null_yields_null

bit

1 = CONCAT_NULL_YIELDS_NULL setting is ON for the request. Otherwise, it is 0.

Is not nullable.

transaction_isolation_level

smallint

Transaction isolation level of this request. Possible values are as follows:

0 = Unspecified

1 = ReadUncomitted

2 = ReadCommitted

3 = Repeatable

4 = Serializable

5 = Snapshot

Is not nullable.

lock_timeout

int

Lock time-out period in milliseconds for this request. Is not nullable.

deadlock_priority

int

DEADLOCK_PRIORITY setting for the request. Is not nullable.

row_count

bigint

Number of rows that have been returned to the client by this request. Is not nullable.

prev_error

int

Last error that occurred during the execution of the request. Is not nullable.

nest_level

int

Nesting level of code that is executing on the request. Is not nullable.

granted_query_memory

int

Number of pages allocated to the execution of a query on the request. Is not nullable.

executing_managed_code

bit

Indicates whether this request is 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 when it runs Transact-SQL from common language runtime. Is not nullable.

Permissions

Requires VIEW SERVER STATE permission on the server.

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 session in which sys.dm_exec_requests is executed.

Relationship Cardinalities

From	To	On/Apply	Relationship
sys.dm_exec_sessions	sys.dm_exec_requests	session_id	One to zero or many
sys.dm_exec_requests	sys.dm_exec_sql_text(sql_handle)	CROSS APPLY OUTER APPLY	Zero or one to zero or one
sys.dm_exec_requests	sys.dm_exec_query_plan(plan_handle)	CROSS APPLY OUTER APPLY	Zero or one to zero or one
sys.dm_exec_requests	sys.dm_exec_cached_plans	plan_handle	Zero or one to zero or one
sys.dm_exec_requests	sys.dm_exec_plan_attributes(plan_handle)	CROSS APPLY OUTER APPLY	Zero or one to zero or one
sys.dm_exec_requests	sys.databases	database_id	One to one
sys.dm_exec_requests	sys.database_principals	user_id = principal_id	One to one
sys.dm_exec_connections	sys.dm_exec_requests	connection_id	One to zero or one
sys.dm_exec_requests	sys.dm_tran_active_transactions	transaction_id	One to one

Examples

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

Reference

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
Dynamic Management Views and Functions
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

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added mapping from SQL Server 2000 to SQL Server 2005. Added the examples. Added the Relationship Cardinalities table. Changed content: Changed descriptions for start_time, status, sql_handle, plan_handle, database_id, user_id, connection_id, transaction_id, context_info, scheduler_id, task_address, language, date_format, transaction_isolation_level, date_first, executing_managed_code.
5 December 2005	New content: Added the note regarding time spent in preemptive mode. Changed content: Corrected data type of wait_type.

Release

History

12 December 2006

New content:

Added mapping from SQL Server 2000 to SQL Server 2005.
Added the examples.
Added the Relationship Cardinalities table.

Changed content:

Changed descriptions for start_time, status, sql_handle, plan_handle, database_id, user_id, connection_id, transaction_id, context_info, scheduler_id, task_address, language, date_format, transaction_isolation_level, date_first, executing_managed_code.

5 December 2005

New content:

Added the note regarding time spent in preemptive mode.

Changed content:

Corrected data type of wait_type.

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD