sys.dm_exec_plan_attributes (Transact-SQL)

適用対象:SQL Server

プラン ハンドルで指定されたプランのプラン属性ごとに 1 行を返します。 このテーブル値関数を使用すると、キャッシュ キー値やプランの現在の同時実行の数など、特定のプランに関する詳細を取得できます。

注意

この関数から返される情報の一部は、 sys.syscacheobjects 下位互換性ビューにマップされます。

構文

sys.dm_exec_plan_attributes ( plan_handle )  

引数

plan_handle
実行され、プランがプラン キャッシュに存在するバッチのクエリ プランを一意に識別します。 plan_handlevarbinary(64) です。 プラン ハンドルは、 sys.dm_exec_cached_plans 動的管理ビューから取得できます。

返されるテーブル

列名 データ型 説明
属性 (attribute) varchar(128) このプランに関連付けられている属性の名前。 この 1 つのすぐ下の表に、使用可能な属性、データ型、およびそれらの説明が一覧表示されています。
value sql_variant このプランに関連付けられている属性の値。
is_cache_key bit 属性がプランのキャッシュ参照キーの一部として使用されるかどうかを示します。

上記の表から、 属性 には次の値を指定できます。

属性 データ型 説明
set_options int プランがコンパイルされたオプション値を示します。
objectid int キャッシュ内のオブジェクトを検索するために使用される主要なキーの 1 つ。 これは、データベース オブジェクト (プロシージャ、ビュー、トリガーなど) の sys.objects に格納されているオブジェクト ID です。 "アドホック プラン" または "準備されたプラン" では、バッチ テキストの内部ハッシュです。
dbid int プランが参照するエンティティを含むデータベースの ID です。

アドホックプランまたは準備済みプランの場合は、バッチの実行元のデータベース ID です。
dbid_execute int リソース データベースに格納されているシステム オブジェクトの場合、キャッシュされたプランの実行元のデータベース 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 クエリ プランがコンパイルされたコンテキストでデータベースに設定された互換性レベルを表します。 返される互換性レベルは、adhoc ステートメントの現在のデータベース コンテキストの互換性レベルであり、クエリ ヒント の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 データベースのみ。
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 の各サービス目標で、エラスティック プール内のデータベースの場合は、サーバー管理者アカウントまたは Azure Active Directory 管理者アカウントが必要です。 その他のすべてのSQL Databaseサービス目標では、データベースでVIEW DATABASE 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)、Parallel Plan(2)、ANSI_PADDING (1) です。

オプション
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 に達するまで列の値から値を減算します。 減算する各値は、クエリ プランに使用されたカーソル オプションに対応しています。

オプション
なし 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)