sys.dm_exec_query_statistics_xml (Transact-SQL)

發行項
12/19/2023

適用於： SQL Server 2016 (13.x) 和更新版本 Azure SQL Database Azure SQL 受控執行個體

傳回進行中要求的查詢執行計劃。使用此 DMV 擷取有暫時性統計資料的 showplan XML。

語法

sys.dm_exec_query_statistics_xml(session_id)

引數

session_id
這是執行要查閱的批次所用的工作階段識別碼。session_id 為 Smallint。您可以從下列動態管理物件中取得 session_id：

傳回的資料表

資料行名稱	資料類型	描述
session_id	smallint	工作階段的識別碼。不可為 Null。
request_id	int	要求的識別碼。不可為 Null。
sql_handle	varbinary(64)	這是指唯一識別所屬批次或預存程序的 Token。可為 Null。
plan_handle	varbinary(64)	這是指唯一識別目前執行中批次的查詢執行計劃所用的權杖。可為 Null。
query_plan	xml	包含以包含部份統計資料的 plan_handle 指定的查詢執行計劃所用的執行階段執行程序表表示法。顯示計畫是 XML 格式。每個包含諸如特定 Transact-SQL 陳述式、預存程序呼叫和使用者定義函式呼叫的批次，都會產生一份計劃。可為 Null。

備註

重要

使用 sys.dm_exec_query_statistics_xml DMV 執行監視預存程序時，由於可能的隨機存取違規 (AV)，Showplan XML 屬性 <ParameterList> 值 ParameterRuntimeValue 已在 SQL Server 2017 (14.x) CU 26 和 SQL Server 2019 (15.x) CU 12 中移除。針對長時間執行的預存程序進行疑難排解時，此值可能很有用。

從 SQL Server 2017 (14.x) CU 31 和 SQL Server 2019 (15.x) CU 19 開始，已重新啟用 Showplan XML 屬性 <ParameterList> 值的 ParameterRuntimeValue 集合，其中包含追蹤旗標 2446。此追蹤旗標會啟用執行時間參數值的收集，但代價是引入額外的負荷。

警告

追蹤旗標 2446 的用途並非在生產環境中持續啟用，而是僅用於限期的疑難排解。使用此追蹤旗標將引入額外的可能重大 CPU 和記憶體額外負荷，因為無論是否呼叫 sys.dm_exec_query_statistics_xml DMV，我們都會使用執行時間參數資訊來建立 Showplan XML 片段。

注意

從 SQL Server 2022 (16.x)、Azure SQL Database 和 Azure SQL 受控執行個體開始，若要在資料庫層級完成這項作業，請參閱 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) 中的 FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION 選項。

從 SQL Server 2016 (13.x) SP1 開始，即可使用此系統函式。請參閱 KB 3190871

此系統函式可在標準和輕量型查詢執行統計資料分析基礎結構下運作。如需詳細資訊，請參閱查詢分析基礎結構。

在下列狀況之下，sys.dm_exec_query_statistics_xml 傳回的資料表之中的 query_plan 資料行不會傳回任何顯示計劃輸出：

如果已不再執行對應於指定 session_id 的查詢計劃，傳回資料表的 query_plan 資料行會是 null。例如，如果從擷取計劃控制代碼到 sys.dm_exec_query_statistics_xml 使用計劃控制代碼之間，延遲了一段時間，就可能出現這個情況。

由於 xml 資料類型允許的巢狀層級數目有限制，因此 sys.dm_exec_query_statistics_xml 無法傳回巢狀元素等於或超過 128 個層級的查詢計劃。在舊版 SQL Server 中，這種情況會使查詢計劃無法傳回並產生錯誤 6335。在 SQL Server 2005 (9.x) Service Pack 2 和更新版本中，query_plan 資料行會傳回 NULL。

權限

在 SQL Server 上，需要伺服器上的 VIEW SERVER STATE 權限。
在 SQL Database 進階版層級需要資料庫的 VIEW DATABASE STATE 權限。 SQL Database 標準版和基本版層級需要伺服器管理員或 Microsoft Entra 管理員帳戶。

SQL Server 2022 和更新版本的權限

需要伺服器上的 VIEW SERVER PERFORMANCE STATE 權限。

範例

A. 查看執行中批次的即時查詢計劃和執行統計資料

下列範例會查詢 sys.dm_exec_requests 尋找重要查詢並且從輸出複製其 session_id。

SELECT * FROM sys.dm_exec_requests;  
GO

然後，若要取得即時查詢計劃和執行統計資料，請使用與系統函式 sys.dm_exec_query_statistics_xml 一起複製的 session_id。

--Run this in a different session than the session in which your query is running.
SELECT * FROM sys.dm_exec_query_statistics_xml(< copied session_id >);  
GO

或針對所有執行中的要求合併。

--Run this in a different session than the session in which your query is running.
SELECT 
	eqs.query_plan, 
	er.session_id, 
	er.request_id, 
	er.database_id,
	er.start_time,
	er.[status], 
	er.wait_type,
	er.wait_resource, 
	er.last_wait_type,
	(er.cpu_time/1000) AS cpu_time_sec,
	(er.total_elapsed_time/1000)/60 AS elapsed_time_minutes,
	(er.logical_reads*8)/1024 AS logical_reads_KB,
	er.granted_query_memory,
	er.dop,
	er.row_count, 
	er.query_hash, 
	er.query_plan_hash
FROM sys.dm_exec_requests er
	CROSS APPLY sys.dm_exec_query_statistics_xml(session_id) eqs
WHERE er.session_id <> @@spid;
GO

另請參閱

追蹤旗標
 動態管理檢視和函數 (Transact-SQL)
資料庫相關的動態管理檢視 (Transact-SQL)