sys.dm_exec_cached_plans (Transact-SQL)

適用対象: はいSQL Server (サポートされているすべてのバージョン) はいAzure SQL データベース はいAzure SQL Managed Instance はいAzure Synapse Analytics はいParallel Data Warehouse

クエリ実行を高速化するため SQL Server でキャッシュされた各クエリ プランについての行を返します。 この動的管理ビューを使用して、キャッシュされたクエリ プラン、キャッシュされたクエリ テキスト、キャッシュされたプランが確保するメモリの量、およびキャッシュされたプランの再利用回数を参照できます。

Azure SQL データベース では、動的管理ビューは、データベースの包含に影響する情報を公開することも、ユーザーがアクセスできる他のデータベースに関する情報を公開することもできません。 この情報が公開されるのを避けるために、接続されているテナントに属していないデータを含むすべての行がフィルター処理されます。さらに、列と列の 値memory_object_addressフィルター pool_id されます。列の値は NULL に設定されます。

注意

または からこれを呼び出 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 です。 Bound Trees のキャッシュでは、ハッシュ テーブルのサイズが 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 と併用してエントリをキャッシュするコストを取得したりできます。
cacheobjtype nvarchar(34) キャッシュ内のオブジェクトの種類。 値は次のいずれかになります。

Compiled Plan

Compiled Plan Stub

Parse Tree

Extended Proc

CLR Compiled Func

CLR Compiled Proc
objtype nvarchar(16) オブジェクトの種類。 指定できる値とそれに対応する説明を次に示します。

Proc: ストアド プロシージャ
準備: 準備されたステートメント
アドホック: アドホック クエリ。 リモート プロシージャ Transact-SQL 呼び出しとしてではなく 、osql または sqlcmd を使用して送信された言語イベントを参照します。
ReplProc: Replication-filter-procedure
トリガー: トリガー
表示: 表示
既定値: 既定値
UsrTab: ユーザー テーブル
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、S0、S1 のサービス目標、およびエラスティック プール内のデータベースの場合は、サーバー管理者アカウント 、Azure Active Directory管理者アカウント、またはサーバー ロールのメンバーシップが必要 ##MS_ServerStateReader## です。 他のすべてのサービスSQL Database、データベースに対するアクセス許可、またはサーバー ロールのメンバーシップ VIEW DATABASE STATE ##MS_ServerStateReader## が必要です。

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_optionssql_handle 行ではなく列として出力するために使用されます。 で返される値の詳細については set_options 、「Transact-sys.dm_exec_plan_attributes (」を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)