共用方式為


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_id為 smallint。 您可以從下列動態管理物件中取得 session_id

傳回的資料表

欄位名稱 資料類型 描述
session_id smallint 工作階段的識別碼。 不可為空。
request_id int 要求的識別碼。 不可為 Null。
sql_handle varbinary(64) 可唯一識別查詢所屬批次或預存程式的令牌。 可為空值。
plan_handle varbinary(64) 令牌,可唯一識別目前正在執行的批次查詢執行計劃。 可為空值。
query_plan xml 包含包含部分統計數據之查詢執行計劃的 plan_handle 運行時間 Showplan 表示。 Showplan 以 XML 格式表示。 每個包含諸如特定 Transact-SQL 陳述式、預存程序呼叫和使用者定義函式呼叫的批次,都會產生一份計劃。 可空值

局限性

由於在使用 DMV 執行監視預存程式時可能發生隨機訪問違規(AV),因此在 SQL Server 2017(14.x)CU 26 和 SQL Server 2019(15.x)CU 12 中,已移除 Showplan XML 的屬性<ParameterList>ParameterRuntimeValue。 針對長時間執行的預存程序進行疑難排解時,此值可能很有用。 您可以使用 追蹤旗標 2446,在 SQL Server 2017 (14.x) CU 31、SQL Server 2019 (15.x) CU 19 和更新版本中重新啟用此值。 此追蹤旗標會啟用運行時間參數值的集合,但代價是引進額外的額外負荷。

謹慎

追蹤旗標 2446 的用途並非在生產環境中持續啟用,而是僅用於限期的疑難排解。 使用此追蹤旗標會造成額外且可能顯著的 CPU 和記憶體額外負荷,因為它會建立具有運行時間參數資訊的 Showplan XML 片段,無論 sys.dm_exec_query_statistics_xml 是否呼叫 DMV。

在 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) Service Pack 1 開始,即可使用此系統函式。 如需詳細資訊,請參閱 KB 3190871

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

在下列情況下,與sys.dm_exec_query_statistics_xml相關的結果表中的query_plan欄位不會傳回任何 Showplan 輸出:

  • 如果對應指定 session_id 的查詢計劃已不再執行,所傳回的表格中的 query_plan 欄位將為空值。 例如,當擷取方案控制碼並在使用時之間存在時間延遲時,就可能發生此狀況 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

權限

需要伺服器上的VIEW SERVER STATE許可權,適用於 SQL Server 2019 (15.x)及更早版本。

需要伺服器上的 VIEW SERVER PERFORMANCE STATE 許可權,適用於 SQL Server 2022(16.x)及其更新版本。

在 SQL 資料庫的高級層級中,需要資料庫的 VIEW DATABASE STATE 許可權。

需要在 SQL Database 標準和基本層上擁有 伺服器管理員Microsoft Entra 管理員 帳戶。

範例

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

下列範例會查詢 sys.dm_exec_requests 來尋找感興趣的查詢,並從輸出中複製其 session_id

SELECT *
FROM sys.dm_exec_requests;
GO

然後,若要取得即時查詢計劃和執行統計數據,請使用複製的 session_id 搭配系統函式 sys.dm_exec_query_statistics_xml。 在與查詢執行所在的會話不同的會話中執行此查詢。

SELECT * FROM sys.dm_exec_query_statistics_xml(< copied session_id >);
GO

或者,結合所有正在處理的請求。 在與查詢執行所在的會話不同的會話中執行此查詢。

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 AS er
CROSS APPLY sys.dm_exec_query_statistics_xml(session_id) AS eqs
WHERE er.session_id <> @@SPID;
GO