sys.dm_exec_query_optimizer_info (Transact-SQL)

[アーティクル]
12/19/2023

適用対象: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 と SQL Managed Instance では、VIEW SERVER STATE アクセス許可が必要です。

SQL Database Basic、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の値は変更されず、最適化のみが重要です。

カウンタ	発生回数	Value
最適化	最適化の合計数。	適用なし
elapsed time	最適化の合計数。	個別のステートメント (クエリ) の最適化ごとの平均経過時間 (秒単位)。
最終コスト	最適化の合計数。	内部コスト単位での最適化されたプランの平均推定コスト。
自明な計画	内部使用のみ	内部使用のみ
tasks	内部使用のみ	内部使用のみ
no plan	内部使用のみ	内部使用のみ
search 0	内部使用のみ	内部使用のみ
search 0 time	内部使用のみ	内部使用のみ
search 0 tasks	内部使用のみ	内部使用のみ
検索 1	内部使用のみ	内部使用のみ
検索 1 回	内部使用のみ	内部使用のみ
1 つのタスクを検索する	内部使用のみ	内部使用のみ
検索 2	内部使用のみ	内部使用のみ
search 2 time	内部使用のみ	内部使用のみ
2 つのタスクを検索する	内部使用のみ	内部使用のみ
gain stage 0 to stage 1	内部使用のみ	内部使用のみ
gain stage 1 to stage 2	内部使用のみ	内部使用のみ
タイムアウト	内部使用のみ	内部使用のみ
memory limit exceeded	内部使用のみ	内部使用のみ
stmt の挿入	IN Standard Edition RT ステートメント用の最適化の数。	適用なし
stmt の削除	DELETE ステートメント用の最適化の数。	適用なし
update stmt	UPDATE ステートメントに対する最適化の数。	適用なし
contains subquery	少なくとも 1 つのサブクエリを含むクエリの最適化の数。	適用なし
unnest failed	内部使用のみ	内部使用のみ
tables	最適化の合計数。	最適化された 1 つのクエリあたりの、参照テーブルの平均数。
hints	ヒントが指定された回数。カウントされるヒントには、JOIN、GROUP、UNION、FORCE ORDER クエリヒント、FORCE PLAN セットオプション、結合ヒントが含まれます。	適用なし
order hint	FORCE ORDER ヒントが指定された回数。	適用なし
結合ヒント	結合ヒントによって結合アルゴリズムが強制された回数。	適用なし
view reference	ビューがクエリで参照された回数。	適用なし
remote query	クエリが少なくとも 1 つのリモートデータソース (4 部構成の名前を持つテーブルや OPENROW Standard Edition T 結果など) を参照した最適化の数。	適用なし
最大 DOP	最適化の合計数。	最適化されたプランの平均有効な MAXDOP 値。既定では、有効な MAXDOP は max degree of parallelism サーバー構成オプションによって決定され、特定のクエリに対して MAXDOP クエリヒントの値によってオーバーライドされる場合があります。
maximum recursion level	クエリヒントで 0 より大きい MAXRECURSION レベルが指定された最適化の数。	クエリヒントで最大再帰レベルが指定されている最適化の平均 MAXRECURSION レベル。
indexed views loaded	内部使用のみ	内部使用のみ
indexed views matched	1 つ以上のインデックス付きビューが一致した、最適化の数。	一致したビューの平均数。
使用されるインデックス付きビュー	出力プラン内で照合された後に 1 つ以上のインデックス付きビューが使用されている、最適化の数。	使用されたビューの平均数。
インデックス付きビューの更新	1 つ以上のインデックス付きビューを管理するプランを作成する 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)