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 DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database適用範圍:Applies to: 是 SQL ServerSQL Server (所有支援的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure 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 of the session to which this request is related. 不可為 Null。Is not nullable.
request_idrequest_id intint 要求的識別碼。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
休眠中Sleeping
暫止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
刪除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 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 of the database the request is executing against. 不可為 Null。Is not nullable.
user_iduser_id intint 提交要求之使用者的識別碼。ID of the user who submitted the request. 不可為 Null。Is not nullable.
connection_idconnection_id uniqueidentifieruniqueidentifier 要求到達所用連接的識別碼。ID of the connection on which the request arrived. 可為 Null。Is nullable.
blocking_session_idblocking_session_id smallintsmallint 封鎖要求之工作階段的識別碼。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 = 由於內部閂鎖狀態轉換,目前無法判斷封鎖閂鎖擁有者的工作階段識別碼。-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.

如需等候類型的詳細資訊,請參閱dm_os_wait_stats (transact-sql)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 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
含有 ALTER DATABASE 的 AUTO_SHRINK 選項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 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 = Unspecified0 = Unspecified
1 = ReadUncomitted1 = ReadUncomitted
2 = ReadCommitted2 = ReadCommitted
3 = Repeatable3 = Repeatable
4 = Serializable4 = Serializable
5 = Snapshot5 = 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 指出特定要求目前是否正在執行 Common Language Runtime 物件 (如常式、類型和觸發程序)。Indicates whether a specific request is currently executing common language runtime objects, such as routines, types, and triggers. 這是為 Common Language Runtime 物件在堆疊上的完全時間所設定,即使是從 Common Language Runtime 內部執行 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 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.

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.

與目前要求相關聯的外部腳本要求識別碼。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)

如果資料行包含頁面,則為頁面資源的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 Database 超大規模資料庫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_reads] 和 [ row_count ]。The columns reads, writes, logical_reads, and row_count are not updated for the coordinator thread. 只有協調器執行緒才會更新 wait_typewait_timelast_wait_typewait_resourcegranted_query_memory資料行。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.

權限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 Database 中授與,因此 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 STATEOtherwise, 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

下列範例會查詢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_locks複製的。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.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