分享方式:


sys.dm_exec_query_statistics_xml (Transact-SQL)

適用於:SQL Server 2016 (13.x) 和更新版本 Azure SQL 資料庫 Azure SQL 受控執行個體

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

語法

sys.dm_exec_query_statistics_xml(session_id)  

引數

session_id
這是執行要查閱的批次所用的工作階段識別碼。session_idSmallint。 您可以從下列動態管理物件中取得 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)