分享方式：

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'
);

相關內容