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

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

返回有关在中执行的每个请求的信息 SQL ServerSQL ServerReturns 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 intint 请求的 ID。ID of the request. 在会话的上下文中是唯一的。Unique in the context of the session. 不可为 null。Is not nullable.
start_timestart_time datetimedatetime 请求到达时的时间戳。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
已挂起Suspended

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

SELECTSELECT
INSERTINSERT
UPDATEUPDATE
DELETEDELETE
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 intint 指示当前正在执行的批处理或持久化对象当前正在执行的语句的开始位置(以字节为单位),从0开始。Indicates, in bytes, beginning with 0, the starting position of the currently executing statement for the currently executing batch or persisted object. 可以与 sql_handlestatement_end_offsetsys.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 intint 指示当前正在执行的批处理或持久化对象当前正在执行的语句的结束位置(以字节为单位,从0开始)。Indicates, in bytes, starting with 0, the ending position of the currently executing statement for the currently executing batch or persisted object. 可以与 sql_handlestatement_start_offsetsys.dm_exec_sql_text 动态管理函数一起使用,以检索请求的当前正在执行的语句。Can be used together with the sql_handle, the statement_start_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 intint 提交请求的用户的 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)sys.dm_os_wait_stats For information about types of waits, see sys.dm_os_wait_stats (Transact-SQL).
wait_timewait_time intint 如果请求当前被阻塞,则此列返回当前等待的持续时间(以毫秒为单位)。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 intint 为此请求打开的事务数。Number of transactions that are open for this request. 不可为 null。Is not nullable.
open_resultset_countopen_resultset_count intint 为此请求打开的结果集的个数。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 intint 请求所使用的 CPU 时间(毫秒)。CPU time in milliseconds that is used by the request. 不可为 null。Is not nullable.
total_elapsed_timetotal_elapsed_time intint 请求到达后经过的总时间(毫秒)。Total time elapsed in milliseconds since the request arrived. 不可为 null。Is not nullable.
scheduler_idscheduler_id intint 正在计划此请求的计划程序的 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 intint 此请求的 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 bitbit 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 bitbit 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 intint 此请求的锁定超时时间(毫秒)。Lock time-out period in milliseconds for this request. 不可为 null。Is not nullable.
deadlock_prioritydeadlock_priority intint 请求的 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 intint 在执行请求期间发生的最后一个错误。Last error that occurred during the execution of the request. 不可为 null。Is not nullable.
nest_levelnest_level intint 正在对请求执行的代码的嵌套级别。Current nesting level of code that is executing on the request. 不可为 null。Is not nullable.
granted_query_memorygranted_query_memory intint 为执行该请求的查询而分配的页数。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 intint 此查询所属工作负荷组的 ID。ID of the workload group to which this query belongs. 不可为 null。Is not nullable.
query_hashquery_hash 二进制 (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 二进制 (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 intint 适用于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 intint 适用于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 二进制 (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)

如果列包含页,则为页资源的8字节的十六进制表示形式 wait_resourceAn 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.

在 Always-On 情况下,如果将辅助副本设置为 仅读意向,则与辅助副本的连接必须通过添加在连接字符串参数中指定其应用程序意向 applicationintent=readonlyIn Always-On 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. 否则,访问检查 sys.dm_exec_requests 不会传递到可用性组中的数据库,即使 VIEW SERVER STATE 权限存在也是如此。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.

示例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

下面的示例查询 sys.dm_exec_requests 以查找感兴趣的批,并 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.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

下面的示例查询 sys.dm_exec_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  

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