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	データの種類	説明
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	クエリ プランがコンパイルされたコンテキストでデータベースに設定された互換性レベルを表します。 返される互換性レベルは、アドホック ステートメントの現在のデータベース コンテキストの互換性レベルであり、クエリ ヒント の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 ステートメントの結果として実行される IN Standard Edition RT トリガー プランを示します。 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 のサービス目標と、エラスティック プール内のデータベースの場合は、 サーバー管理者 アカウントまたは Microsoft Entra 管理者 アカウントが必要です。 他のすべての SQL Database サービス目標では、データベースで VIEW DATABASE STATE アクセス許可が必要です。

SQL Server 2022 以降でのアクセス許可

サーバーに対する VIEW SERVER PERFORMANCE STATE アクセス許可が必要です。

解説

SET オプション

同じコンパイル済みプランのコピーは、set_options列の値によってのみ異なる場合があります。 これは、異なる接続が同じクエリに対して異なるStandard Edition T オプションのセットを使用していることを示します。 通常、異なるオプション セットを使用することは望ましくありません。異なるオプション セットを使用すると、余分なコンパイルが発生し、プランの再利用が減少して、キャッシュ内にプランの複数のコピーが存在することが原因でプラン キャッシュが増加します。

設定オプションの評価

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 BROW Standard Edition 操作を実装するために作業テーブルがプランで使用されていないことを示します。	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
ローカル	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)