sys.dm_exec_plan_attributes (Transact-SQL)
適用対象: 
SQL Server
プラン ハンドルによって指定されたプランのプラン属性ごとに 1 行を返します。 このテーブル値関数を使用すると、キャッシュ キー値やプランの現在の同時実行数など、特定のプランに関する詳細を取得できます。
Note
この関数によって返される情報の一部は、 sys.syscacheobjects 下位互換性ビューにマップされます。
構文
sys.dm_exec_plan_attributes ( plan_handle )
引数
plan_handle
実行され、プランがプラン キャッシュに存在するバッチのクエリ プランを一意に識別します。 plan_handle は varbinary(64) です。 プラン ハンドルは、 sys.dm_exec_cached_plans 動的管理ビューから取得できます。
返されるテーブル
| 列名 | データ型 | 説明 |
|---|---|---|
| 属性 | varchar(128) | このプランに関連付けられている属性の名前。 このテーブルのすぐ下に、使用可能な属性、データ型、およびそれらの説明が一覧表示されます。 |
| 値 | sql_variant | このプランに関連付けられている属性の値。 |
| is_cache_key | bit | 属性がプランのキャッシュ参照キーの一部として使用されるかどうかを示します。 |
上記の表から、 attribute には次の値を指定できます。
| Attribute | データの種類 | 説明 |
|---|---|---|
| set_options | int | プランがコンパイルされたオプション値を示します。 |
| objectid | int | キャッシュ内のオブジェクトを検索するために使用されるメイン キーの 1 つ。 これは、データベース オブジェクト (プロシージャ、ビュー、トリガーなど) の sys.objects に格納されているオブジェクト ID です。 "アドホック プラン" または "準備されたプラン" では、バッチ テキストの内部ハッシュです。 |
| dbid | int | プランが参照するエンティティを含むデータベースの ID です。 アドホック プランまたは準備済みプランの場合、バッチの実行元のデータベース ID です。 |
| dbid_execute | int | Resource データベースに格納されているシステム オブジェクトの場合、キャッシュされたプランの実行元のデータベース ID。 その他の場合は 0 になります。 |
| user_id | int | 値 -2 は、送信されたバッチが暗黙的な名前解決に依存せず、異なるユーザー間で共有できることを示します。 可能であればこの方法の使用をお勧めします。 他の値は、データベースのクエリを送っているユーザーのユーザー ID を示します。 |
| language_id | smallint | キャッシュ オブジェクトを作成した接続の言語の ID。 詳細については、「 sys.syslanguages (Transact-SQL)」を参照してください。 |
| date_format | smallint | キャッシュ オブジェクトを作成した接続の日付形式。 詳細については、「SET DATEFORMAT (Transact-SQL)」を参照してください。 |
| date_first | tinyint | 日付の最初の値。 詳細については、「SET DATEFIRST (Transact-SQL)」を参照してください。 |
| compat_level | tinyint | クエリ プランがコンパイルされたコンテキストでデータベースに設定された互換性レベルを表します。 返される互換性レベルは、アドホック ステートメントの現在のデータベース コンテキストの互換性レベルであり、クエリ ヒントの QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_nの影響を受けません。 ストアド プロシージャまたは関数に含まれるステートメントの場合、ストアド プロシージャまたは関数が作成されるデータベースの互換性レベルに対応します。 |
| status | int | キャッシュ参照キーの一部である内部ステータス ビットです。 |
| required_cursor_options | int | ユーザーが指定したカーソル オプション (カーソルの種類など)。 |
| acceptable_cursor_options | int | ステートメントの実行をサポートするために SQL Server が暗黙的に変換できるカーソル オプション。 たとえば、ユーザーは動的カーソルを指定できますが、クエリ オプティマイザーはこのカーソルの種類を静的カーソルに変換できます。 |
| merge_action_type | smallint | MERGE ステートメントの結果として使用されるトリガー実行プランの種類。 0 は、非トリガー計画、MERGE ステートメントの結果として実行されないトリガー計画、または DELETE アクションのみを指定する MERGE ステートメントの結果として実行されるトリガー計画を示します。 1 は、MERGE ステートメントの結果として実行される INSERT トリガープランを示します。 2 は、MERGE ステートメントの結果として実行される UPDATE トリガー プランを示します。 3 は、対応する INSERT アクションまたは UPDATE アクションを含む MERGE ステートメントの結果として実行される DELETE トリガー プランを示します。 連鎖アクションによって実行される入れ子になったトリガーの場合、この値はカスケードの原因となった MERGE ステートメントのアクションです。 |
| is_replication_specific | int | このプランのコンパイル元のセッションが、ドキュメントに記載されていない接続プロパティを使用して SQL Server のインスタンスに接続されたセッションであることを表します。これにより、サーバーはレプリケーション コンポーネントによって作成されたセッションとして識別され、レプリケーション コンポーネントが期待する内容に応じてサーバーの特定の機能の動作が変更されます。 |
| optional_spid | smallint | 接続session_id (spid) は、再コンパイルの数を減らすためにキャッシュ キーの一部になります。 これにより、動的にバインドされていない一時テーブルを含むプランを 1 つのセッションで再利用する際の再コンパイルが防止されます。 |
| optional_clr_trigger_dbid | int | CLR DML トリガーの場合にのみ設定されます。 エンティティを含むデータベースの ID。 その他のオブジェクト型の場合は、0 を返します。 |
| optional_clr_trigger_objid | int | CLR DML トリガーの場合にのみ設定されます。 sys.objectsに格納されているオブジェクト ID。 その他のオブジェクト型の場合は、0 を返します。 |
| parent_plan_handle | varbinary(64) | 常に NULL です。 |
| is_azure_user_plan | tinyint | ユーザーによって開始されたセッションから Azure SQL Database で実行されるクエリの場合は 1。 エンド ユーザーによって開始されず、Azure インフラストラクチャ内から実行され、テレメトリの収集や管理タスクの実行の他の目的でクエリを発行するアプリケーションによって実行されたセッションから実行されたクエリの場合は 0。 is_azure_user_plan = 0 のクエリによって使用されるリソースに対して課金されません。 Azure SQL Database のみ 。 |
| inuse_exec_context | int | クエリ プランを使用している現在実行中のバッチの数。 |
| free_exec_context | int | 現在使用されていないクエリ プランのキャッシュされた実行コンテキストの数。 |
| hits_exec_context | int | 実行コンテキストがプラン キャッシュから取得され、再利用された回数。SQL ステートメントを再コンパイルするオーバーヘッドを節約します。 この値は、これまでのすべてのバッチ実行の集計です。 |
| misses_exec_context | int | プラン キャッシュで実行コンテキストが見つからなかった回数。その結果、バッチ実行の新しい実行コンテキストが作成されます。 |
| removed_exec_context | int | キャッシュされたプランのメモリ不足のために削除された実行コンテキストの数。 |
| inuse_cursors | int | キャッシュされたプランを使用している 1 つ以上のカーソルを含む現在実行中のバッチの数。 |
| free_cursors | int | キャッシュされたプランのアイドルカーソルまたは空きカーソルの数。 |
| hits_cursors | int | キャッシュされたプランから非アクティブなカーソルが取得され、再利用された回数。 この値は、これまでのすべてのバッチ実行の集計です。 |
| misses_cursors | int | キャッシュに非アクティブなカーソルが見つからなかった回数。 |
| removed_cursors | int | キャッシュされたプランのメモリ不足のために削除されたカーソルの数。 |
| sql_handle | varbinary(64) | バッチの SQL ハンドルです。 |
アクセス許可
SQL Server では、VIEW SERVER STATE 権限が必要です。
Azure SQL Database Basic、S0、S1 サービス目標、およびエラスティック プール内のデータベースの場合は、 server admin アカウントまたは Microsoft Entra admin アカウントが必要です。 他のすべての SQL Database サービス目標では、データベースに VIEW DATABASE STATE アクセス許可が必要です。
SQL Server 2022 以降でのアクセス許可
サーバーに対する VIEW SERVER PERFORMANCE STATE アクセス許可が必要です。
解説
SET オプション
同じコンパイル済みプランのコピーは、 set_options 列の値によってのみ異なる場合があります。 これは、異なる接続が同じクエリに対して異なるセットの SET オプションを使用していることを示します。 通常、異なるオプション セットを使用することは望ましくありません。異なるオプション セットを使用すると、余分なコンパイルが発生し、プランの再利用が減少して、キャッシュ内にプランの複数のコピーが存在することが原因でプラン キャッシュが増加します。
設定オプションの評価
set_optionsで返された値を、プランがコンパイルされたオプションに変換するには、0 に達するまで、set_options値から最大の値から値を減算します。 減算する各値は、クエリ プランに使用されたオプションに対応しています。 たとえば、 set_options の値が 251 の場合、プランがコンパイルされたオプションは、ANSI_NULL_DFLT_ON (128)、QUOTED_IDENTIFIER (64)、ANSI_NULLS(32)、ANSI_WARNINGS (16)、CONCAT_NULL_YIELDS_NULL (8)、並列プラン (2)、ANSI_PADDING (1) です。
| オプション | Value |
|---|---|
| ANSI_PADDING | 1 |
| ParallelPlan プランの並列処理オプションが変更されたことを示します。 |
2 |
| FORCEPLAN | 4 |
| CONCAT_NULL_YIELDS_NULL | 8 |
| ANSI_WARNINGS | 16 |
| ANSI_NULLS | 32 |
| QUOTED_IDENTIFIER | 64 |
| ANSI_NULL_DFLT_ON | 128 |
| ANSI_NULL_DFLT_OFF | 256 |
| NoBrowseTable FOR BROWSE 操作を実装するために作業テーブルがプランで使用されていないことを示します。 |
512 |
| TriggerOneRow AFTER トリガーデルタテーブルの単一行の最適化がプランに含まれていることを示します。 |
1024 |
| ResyncQuery クエリが内部システム ストアド プロシージャによって送信されたことを示します。 |
2048 |
| ARITH_ABORT | 4096 |
| NUMERIC_ROUNDABORT | 8192 |
| DATEFIRST | 16384 |
| DATEFORMAT | 32768 |
| LanguageID | 65536 |
| に プランのコンパイル時にデータベース オプション PARAMETERIZATION が FORCED に設定されたことを示します。 |
131072 |
| ROWCOUNT | 適用対象: SQL Server 2012 (11.x) 以降 262144 |
カーソル
非アクティブなカーソルは、カーソルの格納に使用されたメモリをカーソルの同時ユーザーが再利用できるように、コンパイル済みプランにキャッシュされます。 たとえば、カーソルの割り当てを解除せずに、バッチでそのカーソルを宣言して使用するとします。 2 人のユーザーが同じバッチを実行している場合、アクティブなカーソルが 2 つになります。 カーソルの割り当てが解除されると (異なるバッチ内にある可能性があります)、カーソルの格納に使用されるメモリはキャッシュされ、解放されません。 この非アクティブカーソルのリストは、コンパイル済みプランに保持されます。 次にユーザーがバッチを実行するときに、キャッシュされたカーソルのメモリが再利用され、アクティブなカーソルとして適切に初期化されます。
カーソル オプションの評価
required_cursor_optionsで返された値を変換し、acceptable_cursor_optionsプランがコンパイルされたオプションに変換するには、0 に達するまで列の値から値を減算します。 減算する各値は、クエリ プランに使用されたカーソル オプションに対応しています。
| オプション | Value |
|---|---|
| なし | 0 |
| INSENSITIVE | 1 |
| SCROLL | 2 |
| READ ONLY | 4 |
| FOR UPDATE | 8 |
| LOCAL | 16 |
| GLOBAL | 32 |
| FORWARD_ONLY | 64 |
| KEYSET | 128 |
| DYNAMIC | 256 |
| SCROLL_LOCKS | 512 |
| OPTIMISTIC | 1024 |
| STATIC | 2048 |
| FAST_FORWARD | 4096 |
| IN PLACE | 8192 |
| FOR select_statement | 16384 |
例
A. 特定のプランの属性を返す
次の例では、指定したプランのすべてのプラン属性を返します。 sys.dm_exec_cached_plans動的管理ビューが最初に照会され、指定されたプランのプラン ハンドルが取得されます。 2 番目のクエリで、 <plan_handle> を最初のクエリのプラン ハンドル値に置き換えます。
SELECT plan_handle, refcounts, usecounts, size_in_bytes, cacheobjtype, objtype
FROM sys.dm_exec_cached_plans;
GO
SELECT attribute, [value], is_cache_key
FROM sys.dm_exec_plan_attributes(<plan_handle>);
GO
B. コンパイル済みプランの SET オプションとキャッシュされたプランの SQL ハンドルを返す
次の例では、各プランがコンパイルされたオプションを表す値を返します。 さらに、キャッシュされたすべてのプランの SQL ハンドルが返されます。
SELECT plan_handle, pvt.set_options, pvt.sql_handle
FROM (
SELECT plan_handle, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE cacheobjtype = 'Compiled Plan') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;
GO
参照
動的管理ビューと動的管理関数 (Transact-SQL)
実行関連の動的管理ビューと関数 (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.databases (Transact-SQL)
sys.objects (Transact-SQL)