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;  

另請參閱

動態管理檢視和函數 (Transact-SQL)
執行相關的動態管理檢視和函數 (Transact-SQL)