sys.dm_exec_plan_attributes (Transact-SQL)
プラン ハンドルで指定したプランのプラン属性ごとに 1 行のデータを返します。このテーブル値関数を使用すると、キャッシュ キーの値やプランの同時実行数など、特定のプランに関する詳細情報を取得できます。
.gif "注意") 注 |
|---|
この関数によって返される情報には、旧バージョンと互換性のある sys.syscacheobjects ビューにマップされるものもあります。 |
構文
sys.dm_exec_plan_attributes ( plan_handle )
引数
- plan_handle
既に実行されていて、そのプランがプラン キャッシュに格納されているバッチのクエリ プランを一意に識別します。plan_handle のデータ型は varbinary(64) です。プラン ハンドルは、sys.dm_exec_cached_plans 動的管理ビューから取得できます。
返されるテーブル
列名 |
データ型 |
説明 |
|---|---|---|
attribute |
varchar(128) |
このプランに関連付けられている属性の名前。次のいずれかです。
属性データ型説明
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)」を参照してください。
status int キャッシュ参照キーの一部である内部ステータス ビットです。
required_cursor_options int カーソルの種類など、ユーザーによって指定されたカーソル オプションです。
acceptable_cursor_options int SQL Server がステートメントの実行をサポートするために暗黙的に変換できるカーソル オプションです。たとえば、ユーザーは動的カーソルを指定することがありますが、クエリ オプティマイザでは、このカーソルの種類を静的カーソルに変換することが許可されています。詳細については、「暗黙のカーソル変換の使用」を参照してください。
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 ハンドルです。
merge_action_type smallint MERGE ステートメントの結果として使用するトリガの実行プランの種類。 0 は、非トリガ プラン (MERGE ステートメントの結果として実行されないトリガ プラン)、または DELETE アクションのみを指定する MERGE ステートメントの結果として実行されるトリガ プランを示します。 1 は、MERGE ステートメントの結果として実行される INSERT トリガ プランを示します。 2 は、MERGE ステートメントの結果として実行される UPDATE トリガ プランを示します。 3 は、対応する INSERT アクションまたは UPDATE アクションを含む MERGE ステートメントの結果として実行される DELETE トリガ プランを示します。 連鎖操作によって実行される入れ子のトリガの場合、この値は、連鎖操作の原因となった MERGE ステートメントのアクションです。
|
value |
sql_variant |
プランに関連付けられている属性の値。 |
is_cache_key |
bit |
属性が、プランに対するキャッシュ参照キーの一部として使用されているかどうかを示します。 |
権限
サーバーに対する VIEW SERVER STATE 権限が必要です。
説明
SET オプション
同一のコンパイル済みプランのコピーでは、set_options 列の値のみが異なる場合があります。これは、異なる接続では、同じクエリに対して異なる SET オプション セットが使用されていることを示します。通常、異なるオプション セットを使用することは望ましくありません。異なるオプション セットを使用すると、余分なコンパイルが発生し、プランの再利用が減少して、キャッシュ内にプランの複数のコピーが存在することが原因でプラン キャッシュが増加します。詳細については、「クエリ チューニングに関する推奨設定」を参照してください。
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)、Parallel Plan(2)、および ANSI_PADDING (1) になります。
オプション |
値 |
|---|---|
ANSI_PADDING |
1 |
Parallel Plan |
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 トリガ デルタ テーブルに対する 1 行の最適化がプランに含まれていることを示します。 |
1024 |
ResyncQuery クエリが内部システム ストアド プロシージャによって送信されたことを示します。 |
2048 |
ARITH_ABORT |
4096 |
NUMERIC_ROUNDABORT |
8192 |
DATEFIRST |
16384 |
DATEFORMAT |
32768 |
LanguageID |
65536 |
UPON プランがコンパイルされたとき、データベース オプション PARAMETERIZATION が FORCED に設定されたことを示します。 |
131072 |
カーソル
非アクティブなカーソルは、カーソルの格納に使用されたメモリをカーソルの同時ユーザーが再利用できるように、コンパイル済みプランにキャッシュされます。たとえば、カーソルの割り当てを解除せずに、バッチでそのカーソルを宣言して使用するとします。2 人のユーザーが同じバッチを実行している場合、アクティブなカーソルが 2 つになります。(場合によっては別のバッチで) カーソルの割り当てが解除されると、カーソルの格納に使用されたメモリはキャッシュされ、解放されません。この非アクティブなカーソルの一覧は、コンパイル済みプランに保持されます。次にユーザーがバッチを実行するときに、キャッシュされたカーソルのメモリが再利用され、アクティブなカーソルとして適切に初期化されます。
カーソル オプションの評価
required_cursor_options および acceptable_cursor_options に返された値を、プランをコンパイルしたオプションに変換するには、最も大きいと考えられる値から順に 0 に到達するまで、列の値からその値を減算します。減算する各値は、クエリ プランに使用されたカーソル オプションに対応しています。
オプション |
値 |
|---|---|
なし |
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