sys.dm_exec_query_optimizer_info (Transact-SQL)

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

SQL Server クエリ オプティマイザーの操作に関する詳細な統計情報を返します。 このビューは、クエリの最適化の問題や改善点を特定するためにワークロードをチューニングするときに使用できます。 たとえば、最適化の合計数、所要時間、および最終的なコストを使用して、現在のワークロードのクエリの最適化と、チューニング処理中に確認された変更を比較できます。 一部のカウンターでは、SQL Server 内部診断の使用にのみ関連するデータが提供されます。 このようなカウンターには、"内部使用のみ" と記載してあります。

アクセス許可

SQL Server 2019 (15.x) 以前のバージョンと Azure SQL Managed Instance には、 VIEW SERVER STATE アクセス許可が必要です。

SQL Server 2022 (16.x) 以降のバージョンでは、サーバーに対する VIEW SERVER PERFORMANCE STATE アクセス許可が必要です。

Azure SQL Database Basic、S0、および S1 サービス目標、および elastic プール内のデータベースの場合サーバー管理者アカウント、Microsoft Entra 管理者 アカウント、または#MS_ServerStateReader## server ロールのメンバーシップが必要です。 他のすべての SQL Database サービス目標では、データベースに対する 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の値は変更されません。最適化のみが重要です。

例

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

関連するコンテンツ

• 動的管理ビューと動的管理関数 (Transact-SQL)
• 実行関連の動的管理ビューと関数 (Transact-SQL)