sys.dm_exec_plan_attributes (Transact-SQL)

同一のコンパイル済みプランのコピーでは、set_options 列の値のみが異なる場合があります。これは、異なる接続では、同じクエリに対して異なる SET オプション セットが使用されていることを示します。通常、異なるオプション セットを使用することは望ましくありません。異なるオプション セットを使用すると、余分なコンパイルが発生し、プランの再利用が減少して、キャッシュ内にプランの複数のコピーが存在することが原因でプラン キャッシュが増加します。詳細については、「クエリ チューニングに関する推奨設定」を参照してください。

SET オプションの評価

非アクティブなカーソルは、カーソルの格納に使用されたメモリをカーソルの同時ユーザーが再利用できるように、コンパイル済みプランにキャッシュされます。たとえば、カーソルの割り当てを解除せずに、バッチでそのカーソルを宣言して使用するとします。2 人のユーザーが同じバッチを実行している場合、アクティブなカーソルが 2 つになります。(場合によっては別のバッチで) カーソルの割り当てが解除されると、カーソルの格納に使用されたメモリはキャッシュされ、解放されません。この非アクティブなカーソルの一覧は、コンパイル済みプランに保持されます。次にユーザーがバッチを実行するときに、キャッシュされたカーソルのメモリが再利用され、アクティブなカーソルとして適切に初期化されます。

カーソル オプションの評価

次の例では、指定したプランのすべてのプラン属性を返します。最初に、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

次の例では、各プランをコンパイルしたオプションを示す値を返します。さらに、キャッシュされたすべてのプランの 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