sys.dm_exec_requests (Transact-SQL)sys.dm_exec_requests (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

返回有关在中SQL ServerSQL Server执行的每个请求的信息。Returns information about each request that is executing in SQL ServerSQL Server. 有关请求的详细信息,请参阅线程和任务体系结构指南For more information about requests, see the Thread and Task Architecture Guide.

列名称Column name 数据类型Data type 说明Description
session_idsession_id smallintsmallint 与此请求相关的会话的 ID。ID of the session to which this request is related. 不可为 null。Is not nullable.
request_idrequest_id 整形int 请求的 ID。ID of the request. 在会话的上下文中是唯一的。Unique in the context of the session. 不可为 null。Is not nullable.
start_timestart_time datetime 请求到达时的时间戳。Timestamp when the request arrived. 不可为 null。Is not nullable.
statusstatus nvarchar (30)nvarchar(30) 请求的状态。Status of the request. 可以是以下值之一:This can be one of the following:

背景Background
运行Running
可运行Runnable
SleepingSleeping
SuspendedSuspended

不可为 null。Is not nullable.
commandcommand nvarchar (32)nvarchar(32) 标识正在处理的命令的当前类型。Identifies the current type of command that is being processed. 常用命令类型包括:Common command types include the following:

SELECTSELECT
INSERTINSERT
UPDATEUPDATE
删除DELETE
BACKUP LOGBACKUP LOG
BACKUP DATABASEBACKUP DATABASE
DBCCDBCC
FORFOR

可通过结合使用 sys.dm_exec_sql_text 和与请求对应的 sql_handle 检索请求的文本。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 MONITORLOCK MONITOR
CHECKPOINTLAZYCHECKPOINTLAZY
WRITERWRITER

不可为 null。Is not nullable.
sql_handlesql_handle varbinary (64)varbinary(64) 是唯一标识查询所属的批处理或存储过程的标记。Is a token that uniquely identifies the batch or stored procedure that the query is part of. 可以为 Null。Is nullable.
statement_start_offsetstatement_start_offset 整形int 在当前正在执行的批处理或存储过程中,指示当前正在执行的语句开始位置的字符数。Number of characters into the currently executing batch or stored procedure at which the currently executing statement starts. 可以与 sql_handle、statement_end_offset 和 sys.dm_exec_sql_text 动态管理函数一起使用,以便为请求检索当前正在执行的语句。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. 可以为 Null。Is nullable.
statement_end_offsetstatement_end_offset 整形int 在当前正在执行的批处理或存储过程中,指示当前正在执行的语句结束位置的字符数。Number of characters into the currently executing batch or stored procedure at which the currently executing statement ends. 可以与 sql_handle、statement_end_offset 和 sys.dm_exec_sql_text 动态管理函数一起使用,以便为请求检索当前正在执行的语句。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. 可以为 Null。Is nullable.
plan_handleplan_handle varbinary (64)varbinary(64) 是一个标记,用于为当前正在执行的批处理唯一标识查询执行计划。Is a token that uniquely identifies a query execution plan for a batch that is currently executing. 可以为 Null。Is nullable.
database_iddatabase_id smallintsmallint 对其执行请求的数据库的 ID。ID of the database the request is executing against. 不可为 null。Is not nullable.
user_iduser_id 整形int 提交请求的用户的 ID。ID of the user who submitted the request. 不可为 null。Is not nullable.
connection_idconnection_id uniqueidentifieruniqueidentifier 请求到达时所采用的连接的 ID。ID of the connection on which the request arrived. 可以为 Null。Is nullable.
blocking_session_idblocking_session_id smallintsmallint 正在阻塞请求的会话的 ID。ID of the session that is blocking the request. 如果此列的值为 NULL 或等于0,则不会阻止该请求,或者阻塞会话的会话信息不可用(或无法识别)。If this column is NULL or equal to 0, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).

-2 = 阻塞资源由孤立的分布式事务拥有。-2 = The blocking resource is owned by an orphaned distributed transaction.

-3 = 阻塞资源由延迟的恢复事务拥有。-3 = The blocking resource is owned by a deferred recovery transaction.

-4 = 由于内部闩锁状态转换而导致此时无法确定阻塞闩锁所有者的会话 ID。-4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.
wait_typewait_type nvarchar (60)nvarchar(60) 如果请求当前被阻塞,则此列返回等待类型。If the request is currently blocked, this column returns the type of wait. 可以为 Null。Is nullable.

有关等待类型的信息,请参阅transact-sql)(dm_os_wait_stats For information about types of waits, see sys.dm_os_wait_stats (Transact-SQL).
wait_timewait_time 整形int 如果请求当前被阻塞,则此列返回当前等待的持续时间(以毫秒为单位)。If the request is currently blocked, this column returns the duration in milliseconds, of the current wait. 不可为 null。Is not nullable.
last_wait_typelast_wait_type nvarchar (60)nvarchar(60) 如果此请求先前已经阻塞,则此列返回上次等待的类型。If this request has previously been blocked, this column returns the type of the last wait. 不可为 null。Is not nullable.
wait_resourcewait_resource nvarchar(256)nvarchar(256) 如果请求当前被阻塞,则此列返回请求当前等待的资源。If the request is currently blocked, this column returns the resource for which the request is currently waiting. 不可为 null。Is not nullable.
open_transaction_countopen_transaction_count 整形int 为此请求打开的事务数。Number of transactions that are open for this request. 不可为 null。Is not nullable.
open_resultset_countopen_resultset_count 整形int 为此请求打开的结果集的个数。Number of result sets that are open for this request. 不可为 null。Is not nullable.
transaction_idtransaction_id bigintbigint 在其中执行此请求的事务的 ID。ID of the transaction in which this request executes. 不可为 null。Is not nullable.
context_infocontext_info varbinary (128)varbinary(128) 会话 CONTEXT_INFO 值。CONTEXT_INFO value of the session. 可以为 Null。Is nullable.
percent_completepercent_complete realreal 为以下命令完成的工作的百分比:Percentage of work completed for the following commands:

ALTER INDEX REORGANIZEALTER INDEX REORGANIZE
AUTO_SHRINK 选项(带 ALTER DATABASE)AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASEBACKUP DATABASE
DBCC CHECKDBDBCC CHECKDB
DBCC CHECKFILEGROUPDBCC CHECKFILEGROUP
DBCC CHECKTABLEDBCC CHECKTABLE
DBCC INDEXDEFRAGDBCC INDEXDEFRAG
DBCC SHRINKDATABASEDBCC SHRINKDATABASE
DBCC SHRINKFILEDBCC SHRINKFILE
RECOVERYRECOVERY
RESTORE DATABASERESTORE DATABASE
ROLLBACKROLLBACK
TDE ENCRYPTIONTDE ENCRYPTION

不可为 null。Is not nullable.
estimated_completion_timeestimated_completion_time bigintbigint 仅限内部使用。Internal only. 不可为 null。Is not nullable.
cpu_timecpu_time 整形int 请求所使用的 CPU 时间(毫秒)。CPU time in milliseconds that is used by the request. 不可为 null。Is not nullable.
total_elapsed_timetotal_elapsed_time 整形int 请求到达后经过的总时间(毫秒)。Total time elapsed in milliseconds since the request arrived. 不可为 null。Is not nullable.
scheduler_idscheduler_id 整形int 正在计划此请求的计划程序的 ID。ID of the scheduler that is scheduling this request. 不可为 null。Is not nullable.
task_addresstask_address varbinary(8)varbinary(8) 分配给与此请求关联的任务的内存地址。Memory address allocated to the task that is associated with this request. 可以为 Null。Is nullable.
readsreads bigintbigint 此请求执行的读取数。Number of reads performed by this request. 不可为 null。Is not nullable.
Writeswrites bigintbigint 此请求执行的写入数。Number of writes performed by this request. 不可为 null。Is not nullable.
logical_readslogical_reads bigintbigint 此请求已经执行的逻辑读取数。Number of logical reads that have been performed by the request. 不可为 null。Is not nullable.
text_sizetext_size 整形int 此请求的 TEXTSIZE 设置。TEXTSIZE setting for this request. 不可为 null。Is not nullable.
语言language nvarchar(128)nvarchar(128) 该请求的语言设置。Language setting for the request. 可以为 Null。Is nullable.
date_formatdate_format nvarchar (3)nvarchar(3) 该请求的 DATEFORMAT 设置。DATEFORMAT setting for the request. 可以为 Null。Is nullable.
date_firstdate_first smallintsmallint 该请求的 DATEFIRST 设置。DATEFIRST setting for the request. 不可为 null。Is not nullable.
quoted_identifierquoted_identifier bitbit 1 = QUOTED_IDENTIFIER 对于该请求是 ON。1 = QUOTED_IDENTIFIER is ON for the request. 否则,为0。Otherwise, it is 0.

不可为 null。Is not nullable.
arithabortarithabort bitbit 1 = ARITHABORT 设置对于该请求是 ON。1 = ARITHABORT setting is ON for the request. 否则,为0。Otherwise, it is 0.

不可为 null。Is not nullable.
ansi_null_dflt_onansi_null_dflt_on bitbit 1 = ANSI_NULL_DFLT_ON 设置对于该请求是 ON。1 = ANSI_NULL_DFLT_ON setting is ON for the request. 否则,为0。Otherwise, it is 0.

不可为 null。Is not nullable.
ansi_defaultsansi_defaults bitbit 1 = ANSI_DEFAULTS 设置对于该请求是 ON。1 = ANSI_DEFAULTS setting is ON for the request. 否则,为0。Otherwise, it is 0.

不可为 null。Is not nullable.
ansi_warningsansi_warnings bitbit 1 = ANSI_WARNINGS 设置对于该请求是 ON。1 = ANSI_WARNINGS setting is ON for the request. 否则,为0。Otherwise, it is 0.

不可为 null。Is not nullable.
ansi_paddingansi_padding bitbit 1 = ANSI_PADDING 设置对于该请求是 ON。1 = ANSI_PADDING setting is ON for the request.

否则,为0。Otherwise, it is 0.

不可为 null。Is not nullable.
ansi_nullsansi_nulls 小段bit 1 = ANSI_NULLS 设置对于该请求是 ON。1 = ANSI_NULLS setting is ON for the request. 否则,为0。Otherwise, it is 0.

不可为 null。Is not nullable.
concat_null_yields_nullconcat_null_yields_null 小段bit 1 = CONCAT_NULL_YIELDS_NULL 设置对于该请求是 ON。1 = CONCAT_NULL_YIELDS_NULL setting is ON for the request. 否则,为0。Otherwise, it is 0.

不可为 null。Is not nullable.
transaction_isolation_leveltransaction_isolation_level smallintsmallint 创建此请求的事务时使用的隔离级别。Isolation level with which the transaction for this request is created. 不可为 null。Is not nullable.
0 = 未指定0 = Unspecified
1 = 未提交读取1 = ReadUncomitted
2 = 已提交读取2 = ReadCommitted
3 = 可重复3 = Repeatable
4 = 可序列化4 = Serializable
5 = 快照5 = Snapshot
lock_timeoutlock_timeout 整形int 此请求的锁定超时时间(毫秒)。Lock time-out period in milliseconds for this request. 不可为 null。Is not nullable.
deadlock_prioritydeadlock_priority 整形int 请求的 DEADLOCK_PRIORITY 设置。DEADLOCK_PRIORITY setting for the request. 不可为 null。Is not nullable.
row_countrow_count bigintbigint 已由此请求返回到客户端的行数。Number of rows that have been returned to the client by this request. 不可为 null。Is not nullable.
prev_errorprev_error 整形int 在执行请求期间发生的最后一个错误。Last error that occurred during the execution of the request. 不可为 null。Is not nullable.
nest_levelnest_level 整形int 正在对请求执行的代码的嵌套级别。Current nesting level of code that is executing on the request. 不可为 null。Is not nullable.
granted_query_memorygranted_query_memory 整形int 为执行该请求的查询而分配的页数。Number of pages allocated to the execution of a query on the request. 不可为 null。Is not nullable.
executing_managed_codeexecuting_managed_code bitbit 指示特定请求当前是否正在执行公共语言运行时对象,例如例程、类型和触发器。Indicates whether a specific request is currently executing common language runtime objects, such as routines, types, and triggers. 只要某个公共语言运行时对象在堆栈中,就会设置此值,甚至从公共语言运行时中运行 Transact-SQLTransact-SQL 时,也会设置。It is set for the full time a common language runtime object is on the stack, even while running Transact-SQLTransact-SQL from within common language runtime. 不可为 null。Is not nullable.
group_idgroup_id 整形int 此查询所属工作负荷组的 ID。ID of the workload group to which this query belongs. 不可为 null。Is not nullable.
query_hashquery_hash binary (8)binary(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_hashquery_plan_hash binary (8)binary(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_handlestatement_sql_handle varbinary (64)varbinary(64) 适用于: SQL Server 2014 (12.x)SQL Server 2014 (12.x)和更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

单个查询的 SQL 句柄。SQL handle of the individual query.

如果没有为数据库启用查询存储,则此列为 NULL。This column is NULL if Query Store is not enabled for the database.
statement_context_idstatement_context_id bigintbigint 适用于: SQL Server 2014 (12.x)SQL Server 2014 (12.x)和更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

Sys. query_context_settings 的可选外键。The optional foreign key to sys.query_context_settings.

如果没有为数据库启用查询存储,则此列为 NULL。This column is NULL if Query Store is not enabled for the database.
dopdop 整形int 适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)和更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.

查询的并行度。The degree of parallelism of the query.
parallel_worker_countparallel_worker_count 整形int 适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)和更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.

如果这是并行查询,则为保留的并行工作线程数。The number of reserved parallel workers if this is a parallel query.
external_script_request_idexternal_script_request_id uniqueidentifieruniqueidentifier 适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)和更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later.

与当前请求关联的外部脚本请求 ID。The external script request ID associated with the current request.
is_resumableis_resumable bitbit 适用于: SQL Server 2017 (14.x)SQL Server 2017 (14.x)和更高版本。Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) and later.

指示请求是否为可恢复索引操作。Indicates whether the request is a resumable index operation.
page_resourcepage_resource binary (8)binary(8) 适用于:SQL Server 2019 (15.x)SQL Server 2019 (15.x)Applies to: SQL Server 2019 (15.x)SQL Server 2019 (15.x)

如果wait_resource列包含页,则为页资源的8字节的十六进制表示形式。An 8-byte hexadecimal representation of the page resource if the wait_resource column contains a page. 有关详细信息,请参阅sys. fn_PageResCrackerFor more information, see sys.fn_PageResCracker.
page_server_readspage_server_reads bigintbigint 适用于: Azure SQL 数据库超大规模Applies to: Azure SQL Database Hyperscale

此请求执行的页服务器读取次数。Number of page server reads performed by this request. 不可为 null。Is not nullable.
     

备注Remarks

若要执行在 SQL ServerSQL Server 以外的代码(例如,扩展存储过程和分布式查询),则必须在非抢先计划程序的控制范围以外执行该线程。To execute code that is outside SQL ServerSQL 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.

行模式下执行并行请求时SQL ServerSQL Server ,会分配一个工作线程来协调负责完成分配给它们的任务的工作线程。When executing parallel requests in row mode, SQL ServerSQL Server assigns a worker thread to coordinate the worker threads responsible for completing tasks assigned to them. 在此 DMV 中,只有协调器线程对该请求可见。In this DMV only the coordinator thread is visible for the request. 会为协调器线程更新列读取写入logical_readsrow_countThe columns reads, writes, logical_reads, and row_count are not updated for the coordinator thread. 为协调器线程更新列wait_typewait_timelast_wait_typewait_resourcegranted_query_memoryThe 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.

权限Permissions

如果用户具有VIEW SERVER STATE对服务器的权限,则该用户将看到该实例上的所有正在执行SQL ServerSQL Server的会话;否则,用户将只看到当前会话。If the user has VIEW SERVER STATE permission on the server, the user will see all executing sessions on the instance of SQL ServerSQL Server; otherwise, the user will see only the current session. VIEW SERVER STATE无法在 Azure SQL 数据库中授予, sys.dm_exec_requests因此始终限制为当前连接。VIEW SERVER STATE cannot be granted in Azure SQL Database so sys.dm_exec_requests is always limited to the current connection.

示例Examples

A.A. 查找用于运行批处理的查询文本Finding the query text for a running batch

以下示例查询 sys.dm_exec_requests 以查找感兴趣的查询并从输出复制其 sql_handleThe 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  

然后,为了获取语句文本,将复制的 sql_handle 与系统函数 sys.dm_exec_sql_text(sql_handle) 一起使用。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.B. 查找运行的批处理持有的所有锁Finding all locks that a running batch is holding

下面的示例查询dm_exec_requests sys.databases以查找感兴趣的批,并从transaction_id输出复制其。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

然后,若要查找锁定信息,请使用transaction_id与系统函数 sys.databases 一起复制的dm_tran_locksThen, 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.C. 查找当前阻塞的所有请求Finding all currently blocked requests

下面的示例查询dm_exec_requests sys.databases以查找有关被阻止的请求的信息。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  

D.D. 按 CPU 对现有请求进行排序Ordering existing requests by CPU

SELECT 
   req.session_id
   , req.start_time
   , cpu_time 'cpu_time_ms'
   , object_name(st.objectid,st.dbid) 'ObjectName' 
   , substring
      (REPLACE
        (REPLACE
          (SUBSTRING
            (ST.text
            , (req.statement_start_offset/2) + 1
            , (
               (CASE 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 cpu_time desc;
GO

另请参阅See Also

动态管理视图和函数 Dynamic Management Views and Functions
与执行相关的动态管理视图和函数 Execution Related Dynamic Management Views and Functions
sys. dm_os_memory_clerks sys.dm_os_memory_clerks
sys. dm_os_sys_info sys.dm_os_sys_info
sys. dm_exec_query_memory_grants sys.dm_exec_query_memory_grants
sys. dm_exec_query_plan sys.dm_exec_query_plan
sys. dm_exec_sql_text sys.dm_exec_sql_text
SQL Server,SQL Statistics 对象 SQL Server, SQL Statistics Object
查询处理体系结构指南 Query Processing Architecture Guide
线程和任务体系结构指南Thread and Task Architecture Guide