sys.dm_exec_cached_plans (Transact-SQL)

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 このキャッシュ オブジェクトを参照しているキャッシュ オブジェクトの数。 エントリを キャッシュに格納するには、Refcount が少なくとも 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 サービス目標、およびエラスティック プール内のデータベースの場合、サーバー管理者アカウント、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: プランがコンパイルされたStandard Edition T オプションを返す

次の例は、プランをコンパイルした SET オプションを返します。 sql_handleプランの値も返されます。 PIVOT 演算子は、行ではなく列として属性をsql_handle出力set_optionsするために使用されます。 返される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)