sys.dm_exec_query_optimizer_info (Transact-SQL)
適用於:SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)
傳回 SQL Server 查詢最佳化工具作業的詳細統計資料。 調整工作負載時,您可以使用此檢視來識別查詢優化問題或改善。 例如,您可以使用優化總數、經過的時間值和最終成本值來比較目前工作負載的查詢優化,以及微調程式期間觀察到的任何變更。 某些計數器會提供僅與 SQL Server 內部診斷使用相關的資料。 這些計數器會標示為「僅限內部」。
注意
若要從 Azure Synapse Analytics 或 Analytics Platform System (PDW) 呼叫此專案,請使用 名稱 sys.dm_pdw_nodes_exec_query_optimizer_info
。 Azure Synapse Analytics 的無伺服器 SQL 集區不支援此語法。
名稱 | 資料類型 | 描述 |
---|---|---|
計數器 | nvarchar(4000) | 優化工具統計資料事件的名稱。 |
occurrence | bigint | 此計數器的優化事件出現次數。 |
value | float | 每個事件發生的平均屬性值。 |
pdw_node_id | int | 適用於:Azure Synapse Analytics、Analytics Platform System (PDW) 此散發節點的識別碼。 |
權限
在 SQL Server 和 SQL 受控執行個體上,需要 VIEW SERVER STATE
權限。
在SQL 資料庫基本、S0 和 S1 服務目標上,以及彈性集 區中的 資料庫, 需要伺服器管理員 帳戶、 Microsoft Entra 系統管理員 帳戶或伺服器角色 的成員 ##MS_ServerStateReader##
資格。 在所有其他 SQL Database 服務目標上,需要資料庫的 VIEW DATABASE STATE
權限或 ##MS_ServerStateReader##
伺服器角色的成員資格。
SQL Server 2022 及更新版本的權限
需要伺服器上的 VIEW SERVER PERFORMANCE STATE 權限。
備註
sys.dm_exec_query_optimizer_info 包含下列屬性(計數器)。 所有出現值都是累計的,而且會在系統重新開機時設定為 0。 值欄位的所有值都會在系統重新開機時設定為 Null。 指定平均值的所有值資料行值,都會使用與平均值計算中分母相同資料列的發生值。 當 SQL Server 決定dm_exec_query_optimizer_info 的變更 時,會測量所有查詢優化,包括使用者和系統產生的查詢。 執行已快取的計畫不會變更dm_exec_query_optimizer_info 中的 值,只有優化很重要。
計數器 | 發生事項 | 值 |
---|---|---|
優化 | 優化總數。 | 不適用 |
經過的時間 | 優化總數。 | 每個個別語句 (query) 優化的平均經過時間,以秒為單位。 |
最終成本 | 優化總數。 | 內部成本單位中優化計畫的平均估計成本。 |
trivial 方案 | 僅供內部使用 | 僅供內部使用 |
工作 | 僅供內部使用 | 僅供內部使用 |
沒有計劃 | 僅供內部使用 | 僅供內部使用 |
搜尋 0 | 僅供內部使用 | 僅供內部使用 |
search 0 time | 僅供內部使用 | 僅供內部使用 |
搜尋 0 個工作 | 僅供內部使用 | 僅供內部使用 |
搜尋 1 | 僅供內部使用 | 僅供內部使用 |
搜尋 1 次 | 僅供內部使用 | 僅供內部使用 |
搜尋 1 個工作 | 僅供內部使用 | 僅供內部使用 |
搜尋 2 | 僅供內部使用 | 僅供內部使用 |
search 2 time | 僅供內部使用 | 僅供內部使用 |
搜尋 2 個工作 | 僅供內部使用 | 僅供內部使用 |
取得階段 0 到階段 1 | 僅供內部使用 | 僅供內部使用 |
取得階段 1 到階段 2 | 僅供內部使用 | 僅供內部使用 |
timeout | 僅供內部使用 | 僅供內部使用 |
超過記憶體限制 | 僅供內部使用 | 僅供內部使用 |
insert stmt | INSERT 語句的優化數目。 | 不適用 |
delete stmt | DELETE 子句的優化數目。 | 不適用 |
update stmt | UPDATE 語句的優化數目。 | 不適用 |
包含子查詢 | 包含至少一個子查詢之查詢的優化數目。 | 不適用 |
unnest failed | 僅供內部使用 | 僅供內部使用 |
資料表 | 優化總數。 | 每個查詢所參考的平均資料表數目已優化。 |
提示 | 已指定某些提示的次數。 計數的提示包括:JOIN、GROUP、UNION 和 FORCE ORDER 查詢提示、FORCE PLAN set 選項和聯結提示。 | 不適用 |
順序提示 | 已指定強制順序提示的次數。 | 不適用 |
聯結提示 | 聯結演算法被聯結提示強制使用的次數。 | 不適用 |
檢視參考 | 在查詢中參考檢視的次數。 | 不適用 |
遠端查詢 | 查詢至少參考一個遠端資料源的優化數目,例如具有四部分名稱或 OPENROWSET 結果的資料表。 | 不適用 |
DOP 上限 | 優化總數。 | 優化計畫的平均有效 MAXDOP 值。 根據預設,有效的 MAXDOP 是由 平行處理原則 伺服器組態選項的最大程度所決定,而且可由 MAXDOP 查詢提示的值覆寫特定查詢。 |
遞迴層級上限 | 已使用查詢提示指定 MAXRECURSION 層級大於 0 的優化數目。 | 優化中的平均 MAXRECURSION 層級,其中使用查詢提示指定最大遞迴層級。 |
已載入索引檢視表 | 僅供內部使用 | 僅供內部使用 |
已比對索引檢視表 | 已比對一或多個索引檢視的優化數目。 | 相符的檢視平均數目。 |
使用的索引檢視 | 比對之後,輸出計畫中會使用一或多個索引檢視的優化數目。 | 使用的平均檢視數目。 |
已更新索引檢視表 | DML 語句的優化數目,其會產生維護一或多個索引檢視的計畫。 | 維護的平均檢視數目。 |
動態資料指標要求 | 已指定動態資料指標要求的優化數目。 | 不適用 |
快速向前資料指標要求 | 已指定快速向前資料指標要求的優化數目。 | 不適用 |
merge stmt | MERGE 語句的優化數目。 | 不適用 |
範例
A. 檢視優化工具執行的統計資料
此 SQL Server 實例目前的優化器執行統計資料為何?
SELECT * FROM sys.dm_exec_query_optimizer_info;
B. 檢視優化總數
執行了多少個優化?
SELECT occurrence AS Optimizations FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations';
C. 每個優化的平均耗用時間
每個優化的平均耗用時間為何?
SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization
FROM sys.dm_exec_query_optimizer_info WHERE counter = 'elapsed time';
D. 涉及子查詢的優化分數
優化查詢包含子查詢的分數為何?
SELECT (SELECT CAST (occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'contains subquery') /
(SELECT CAST (occurrence AS float)
FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations')
AS ContainsSubqueryFraction;
另請參閱
意見反應
https://aka.ms/ContentUserFeedback。
即將登場:在 2024 年,我們將逐步淘汰 GitHub 問題作為內容的意見反應機制,並將它取代為新的意見反應系統。 如需詳細資訊,請參閱:提交並檢視相關的意見反應