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 内部診断の使用にのみ関連するデータが提供されます。 このようなカウンターには、"内部使用のみ" と記載してあります。
Note
これを 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 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の値は変更されません。最適化のみが重要です。
| カウンタ | 発生回数 | Value |
|---|---|---|
optimizations |
最適化の合計数。 | 適用なし |
elapsed time |
最適化の合計数。 | 個別のステートメント (クエリ) の最適化ごとの平均経過時間 (秒単位)。 |
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 |
少なくとも 1 つのサブクエリを含むクエリの最適化の数。 | 適用なし |
unnest failed |
内部使用のみ | 内部使用のみ |
tables |
最適化の合計数。 | 最適化された 1 つのクエリあたりの、参照テーブルの平均数。 |
hints |
ヒントが指定された回数。 カウントされるヒントには、 JOIN、 GROUP、 UNION と FORCE ORDER クエリ ヒント、 FORCE PLAN セット オプション、結合ヒントが含まれます。 |
適用なし |
order hint |
結合順序が強制された回数。 このカウンターは、 FORCE ORDER ヒントに限定されません。 INNER HASH JOINなど、クエリ内で結合アルゴリズムを指定すると、強制的に結合順序が設定され、カウンターがインクリメントされます。 |
適用なし |
join hint |
結合ヒントによって結合アルゴリズムが強制された回数。 FORCE ORDERクエリ ヒントでは、このカウンターはインクリメントされません。 |
適用なし |
view reference |
クエリでビューが参照される回数。 | 適用なし |
remote query |
クエリが少なくとも 1 つのリモート データ ソース (4 部構成の名前を持つテーブルや OPENROWSET 結果など) を参照した最適化の数。 |
適用なし |
maximum DOP |
最適化の合計数。 | 最適化されたプランの平均有効 MAXDOP 値。 既定では、有効な MAXDOP は max degree of parallelism サーバー構成オプションによって決定され、 MAXDOP クエリ ヒントの値によって特定のクエリに対してオーバーライドされる可能性があります。 |
maximum recursion level |
0より大きいMAXRECURSION レベルがクエリ ヒントで指定された最適化の数。 |
クエリ ヒントで最大再帰レベルが指定された最適化の平均 MAXRECURSION レベル。 |
indexed views loaded |
内部使用のみ | 内部使用のみ |
indexed views matched |
1 つ以上のインデックス付きビューが一致する最適化の数。 | 一致したビューの平均数。 |
indexed views used |
出力プラン内で照合された後に 1 つ以上のインデックス付きビューが使用されている、最適化の数。 | 使用されたビューの平均数。 |
indexed views updated |
1 つ以上のインデックス付きビューを管理するプランを作成する 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'
);