適用於: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