sys.dm_exec_plan_attributes (Transact-SQL)
適用対象:
SQL Server
プラン ハンドルで指定されたプランのプラン属性ごとに 1 行を返します。 このテーブル値関数を使用すると、キャッシュ キー値やプランの現在の同時実行の数など、特定のプランに関する詳細を取得できます。
注意
この関数から返される情報の一部は、 sys.syscacheobjects 下位互換性ビューにマップされます。
構文
sys.dm_exec_plan_attributes ( plan_handle )
引数
plan_handle
実行され、プランがプラン キャッシュに存在するバッチのクエリ プランを一意に識別します。 plan_handle は varbinary(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)