sys.dm_exec_cached_plans (Transact-SQL)
適用対象: 
SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
クエリの実行を高速化するために SQL Server によってキャッシュされる各クエリ プランの行を返します。 この動的管理ビューを使用すると、キャッシュされたクエリ プラン、キャッシュされたクエリ テキスト、キャッシュされたプランによって取得されたメモリの量、キャッシュされたプランの再利用数を検索できます。
Azure SQL Database では、動的管理ビューでは、データベースの包含に影響する情報を公開したり、ユーザーがアクセスできる他のデータベースに関する情報を公開したりすることはできません。 この情報が公開されないようにするために、接続されているテナントに属していないデータを含む行はすべて除外されます。さらに、列 memory_object_address と pool_id の値がフィルター処理されます。列の値は NULL に設定されます。
Note
これを Azure Synapse Analytics または Analytics Platform System (PDW) から呼び出すには、 sys.dm_pdw_nodes_exec_cached_plansという名前を使用します。 この構文は、Azure Synapse Analytics のサーバーレス SQL プールでサポートされていません。
| 列名 | データ型 | 説明 |
|---|---|---|
| bucketid | int | エントリがキャッシュされるハッシュ バケットの ID。 値の範囲は、0 からキャッシュの種類によって決まっているハッシュ テーブルのサイズまでです。 SQL プランとオブジェクト プランのキャッシュの場合、ハッシュ テーブルのサイズは、32 ビット システムでは最大 10007、64 ビット システムでは最大 40009 になります。 バインド ツリー キャッシュの場合、ハッシュ テーブルのサイズは、32 ビット システムでは最大 1009、64 ビット システムでは最大 4001 になります。 拡張ストアド プロシージャのキャッシュでは、ハッシュ テーブルのサイズが 32 ビット システムでも 64 ビット システムでも 127 です。 |
| refcounts | int | このキャッシュ オブジェクトを参照しているキャッシュ オブジェクトの数。 エントリをキャッシュに格納するには refcounts が少なくとも 1 である必要があります。 |
| usecounts | int | キャッシュ オブジェクトが検索された回数。 パラメーター化されたクエリでキャッシュ内のプランが見つかるとインクリメントされません。 プラン表示を使用しているときは複数回増加できます。 |
| size_in_bytes | int | キャッシュ オブジェクトによって使用されたバイト数。 |
| memory_object_address | varbinary(8) | キャッシュ エントリのメモリ アドレス。 この値は、 sys.dm_os_memory_objects と共に使用して、キャッシュされたプランのメモリの内訳を取得し、 sys.dm_os_memory_cache_entries_entriesを使用してエントリをキャッシュするコストを取得できます。 |
| cacheobjtype | nvarchar(34) | キャッシュ内のオブジェクトの種類。 値は次のいずれかになります。 コンパイル済みプラン コンパイル済みプラン スタブ Parse Tree 拡張プロシージャ CLR コンパイル済み Func CLR コンパイル 済みプロシージャ |
| objtype | nvarchar(16) | オブジェクトの種類。 使用可能な値とそれに対応する説明を次に示します。 Proc: ストアド プロシージャ 準備済み: Prepared ステートメント アドホック: アドホック クエリ。 リモート プロシージャ 呼び出しとしてではなく、 osql または sqlcmd を使用して、言語イベントとして送信される Transact-SQL を参照します。 ReplProc: Replication-filter-procedure トリガー: トリガー 表示: 表示 既定値: 既定値 UsrTab: User テーブル SysTab: システム テーブル Check: CHECK 制約 ルール: ルール |
| plan_handle | varbinary(64) | メモリ内プランの識別子。 この識別子は一時的なものであり、プランがキャッシュに残っている間だけ一定です。 この値は、次の動的管理関数で使用できます。 sys.dm_exec_sql_text sys.dm_exec_query_plan sys.dm_exec_plan_attributes |
| pool_id | int | このプランのメモリ使用量の大部分を占めるリソース プールの ID。 |
| pdw_node_id | int | 適用対象: Azure Synapse Analytics、Analytics Platform System (PDW) このディストリビューションがオンになっているノードの識別子。 |
1
アクセス許可
SQL Server と SQL Managed Instance では、VIEW SERVER STATE アクセス許可が必要です。
SQL Database の Basic、S0、S1 サービス対象、および Elastic Pool のデータベースの場合、サーバー管理者アカウント、Microsoft Entra 管理者アカウント、または ##MS_ServerStateReader##サーバー ロールのメンバーシップが必要です。 他のすべての SQL Database サービス目標では、データベースに対する VIEW DATABASE STATE アクセス許可または ##MS_ServerStateReader## サーバー ロールのメンバーシップのいずれかが必要です。
SQL Server 2022 以降でのアクセス許可
サーバーに対する VIEW SERVER PERFORMANCE STATE アクセス許可が必要です。
例
A. 再利用されるキャッシュされたエントリのバッチ テキストを返す
次の例では、複数回使用されたすべてのキャッシュされたエントリの SQL テキストを返します。
SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 1
ORDER BY usecounts DESC;
GO
B. キャッシュされたすべてのトリガーのクエリ プランを返す
次の例では、キャッシュされたすべてのトリガーのクエリ プランを返します。
SELECT plan_handle, query_plan, objtype
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE objtype ='Trigger';
GO
C: プランがコンパイルされた SET オプションを返す
次の例は、プランをコンパイルした SET オプションを返します。 プランの sql_handle も返されます。 PIVOT 演算子は、 set_options 属性と sql_handle 属性を行ではなく列として出力するために使用されます。 set_optionsで返される値の詳細については、「sys.dm_exec_plan_attributes (Transact-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
D. キャッシュされたすべてのコンパイル済みプランのメモリ内訳を返す
次の例では、キャッシュ内のすべてのコンパイル済みプランで使用されるメモリの内訳を返します。
SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,
omo.memory_object_address, type, page_size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
JOIN sys.dm_os_memory_objects AS omo
ON ecp.memory_object_address = omo.memory_object_address
OR ecp.memory_object_address = omo.parent_address
WHERE cacheobjtype = 'Compiled Plan';
GO
参照
動的管理ビューと動的管理関数 (Transact-SQL)
実行関連の動的管理ビューと関数 (Transact-SQL)
sys.dm_exec_query_plan (Transact-SQL)
sys.dm_exec_plan_attributes (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_os_memory_objects (Transact-SQL)
sys.dm_os_memory_cache_entries (Transact-SQL)
FROM (Transact-SQL)