sys.dm_exec_query_optimizer_info （Transact-SQL）
適用於：
SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics 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 集區不支援此語法。
| 名稱 | 資料類型 | 描述 |
|---|---|---|
counter |
nvarchar(4000) | 優化工具統計數據事件的名稱。 |
occurrence |
bigint | 此計數器的優化事件出現次數。 |
value |
float | 每個事件發生的平均屬性值。 |
pdw_node_id |
int | 此散發節點的標識碼。 適用於：Azure Synapse Analytics、Analytics Platform System (PDW) |
權限
SQL Server 2019 （15.x） 和舊版以及 Azure SQL 受控執行個體 需要VIEW SERVER STATE許可權。
SQL Server 2022 （16.x） 和更新版本需要 VIEW SERVER PERFORMANCE STATE 伺服器上的許可權。
在 Azure SQL 資料庫 Basic、S0 和 S1 服務目標上，以及彈性集區中的資料庫、伺服器管理員帳戶、Microsoft Entra 系統管理員帳戶，或 ##MS_ServerStateReader## 伺服器角色的成員資格。 在所有其他 SQL 資料庫 服務目標上，VIEW DATABASE STATE需要資料庫的許可權，或 ##MS_ServerStateReader## 伺服器角色的成員資格。
備註
sys.dm_exec_query_optimizer_info 包含下列屬性（計數器）。 所有出現的值都是累積的，而且會在系統重新啟動時設定為 0 。 值欄位的所有值都會在系統重新啟動時設定為 NULL 。 指定平均值的所有值數據行值，都會使用與平均值計算中分母相同數據列的發生值。 當 SQL Server 決定對 的變更 dm_exec_query_optimizer_info時，會測量所有查詢優化，包括使用者產生的查詢和系統產生的查詢。 執行已經快取的計劃不會變更 中的 dm_exec_query_optimizer_info值，只有優化很重要。
| 計數器 | 發生事項 | 值 |
|---|---|---|
optimizations |
優化總數。 | 不適用 |
elapsed time |
優化總數。 | 每個個別語句 （query） 優化的平均經過時間，以秒為單位。 |
final cost |
優化總數。 | 內部成本單位中優化計劃的平均估計成本。 |
trivial plan |
僅供內部使用 | 僅供內部使用 |
tasks |
僅供內部使用 | 僅供內部使用 |
no plan |
僅供內部使用 | 僅供內部使用 |
search 0 |
僅供內部使用 | 僅供內部使用 |
search 0 time |
僅供內部使用 | 僅供內部使用 |
search 0 tasks |
僅供內部使用 | 僅供內部使用 |
search 1 |
僅供內部使用 | 僅供內部使用 |
search 1 time |
僅供內部使用 | 僅供內部使用 |
search 1 tasks |
僅供內部使用 | 僅供內部使用 |
search 2 |
僅供內部使用 | 僅供內部使用 |
search 2 time |
僅供內部使用 | 僅供內部使用 |
search 2 tasks |
僅供內部使用 | 僅供內部使用 |
gain stage 0 to stage 1 |
僅供內部使用 | 僅供內部使用 |
gain stage 1 to stage 2 |
僅供內部使用 | 僅供內部使用 |
timeout |
僅供內部使用 | 僅供內部使用 |
memory limit exceeded |
僅供內部使用 | 僅供內部使用 |
insert stmt |
語句的 INSERT 優化數目。 |
不適用 |
delete stmt |
語句的 DELETE 優化數目。 |
不適用 |
update stmt |
語句的 UPDATE 優化數目。 |
不適用 |
contains subquery |
包含至少一個子查詢之查詢的優化數目。 | 不適用 |
unnest failed |
僅供內部使用 | 僅供內部使用 |
tables |
優化總數。 | 每個查詢所參考的平均數據表數目已優化。 |
hints |
已指定某些提示的次數。 計數的提示包括： JOIN、 GROUP、 UNION 和 FORCE ORDER 查詢提示、 FORCE PLAN 設定選項和聯結提示。 |
不適用 |
order hint |
強制聯結順序的次數。 此計數器不限於 FORCE ORDER 提示。 在查詢中指定聯結算法，例如 INNER HASH JOIN，也會強制聯結順序，以遞增計數器。 |
不適用 |
join hint |
聯結算法被聯結提示強制使用的次數。 查詢 FORCE ORDER 提示不會遞增此計數器。 |
不適用 |
view reference |
在查詢中參考檢視的次數。 | 不適用 |
remote query |
查詢至少參考一個遠端數據源的優化數目，例如具有四部分名稱或結果的 OPENROWSET 數據表。 |
不適用 |
maximum DOP |
優化總數。 | 優化計劃的平均有效 MAXDOP 值。 根據預設，effective MAXDOP 是由 max degree of parallelism 伺服器組態選項所決定，而且可能會由查詢提示的值 MAXDOP 覆寫特定查詢。 |
maximum recursion level |
使用查詢提示指定之層級大於0的優化MAXRECURSION數目。 |
優化中的平均 MAXRECURSION 層級，其中已使用查詢提示指定最大遞歸層級。 |
indexed views loaded |
僅供內部使用 | 僅供內部使用 |
indexed views matched |
比對一或多個索引檢視的優化數目。 | 相符的檢視平均數目。 |
indexed views used |
比對之後，輸出計劃中會使用一或多個索引檢視的優化數目。 | 使用的平均檢視數目。 |
indexed views updated |
DML 語句的優化數目，其會產生維護一或多個索引檢視的計劃。 | 維護的平均檢視數目。 |
dynamic cursor request |
指定動態數據指標要求的優化數目。 | 不適用 |
fast forward cursor request |
指定快速向前數據指標要求的優化數目。 | 不適用 |
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;
E. 在優化期間檢視提示總數
當包含為查詢提示時 FORCE ORDER ，會計算多少個提示？
-- Check hint count before query execution
SELECT ISNULL('', 0) AS [Before],
[counter],
occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
'hints',
'order hint',
'join hint'
);
SELECT poh.PurchaseOrderID,
poh.OrderDate,
pod.ProductID,
pod.DueDate,
poh.VendorID
FROM Purchasing.PurchaseOrderHeader AS poh
INNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID
OPTION (
FORCE ORDER,
RECOMPILE
);
-- check hint count after query execution
SELECT ISNULL('', 0) AS [After],
[counter],
occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
'hints',
'order hint',
'join hint'
);