適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)
optimize for ad hoc workloads オプションは、多くの単一使用アドホック バッチを含むワークロードのプラン キャッシュの効率を向上させるために使用されます。 このオプションを 1 に設定すると、データベース エンジンでは、バッチが初めてコンパイルされるときに、完全なコンパイル済みプランではなく、コンパイル済みプランの小さいスタブがプラン キャッシュに格納されます。 このオプションにより、再利用されないコンパイル済みプランでプラン キャッシュがいっぱいにならないようにして、メモリの負荷を下げることができる場合があります。 しかし、このオプションを有効にすると、単一使用プランのトラブルシューティング機能に影響する可能性があります。
コンパイル済みプラン スタブは、データベース エンジンがこのアドホック バッチが以前にコンパイルされたことを認識させて、コンパイル済みプラン スタブのみを格納できるようにします。 このバッチが再度呼び出し (コンパイルまたは実行) されると、データベース エンジンはバッチをコンパイルし、コンパイル済みのプラン スタブをプラン キャッシュから削除し、コンパイル済みの完全なプランをプラン キャッシュに追加します。
sys.dm_exec_cached_plans カタログ ビューのクエリを実行し、cacheobjtype 列で "コンパイル済みプラン" を探すことで、コンパイル済みのプラン スタブを見つけることができます。 このスタブには一意の plan_handle が含まれます。 コンパイル済みプランのスタブには、関連付けられた実行プランがないため、プラン ハンドルに対してクエリを実行しても、グラフィックあるいは XML のプラン表示は返されません。
トレース フラグ 8032 は 、キャッシュ制限パラメーターを SQL Server 2005 (9.x) RTM 設定に戻します。一般に、キャッシュのサイズを大きくすることができます。 この設定は、頻繁に再利用されるキャッシュ エントリがキャッシュに収まらない場合や、 optimize for ad hoc workloads オプションでプラン キャッシュの問題を解決できなかった場合に使用します。
警告
トレース フラグ 8032 は、大きなキャッシュでバッファー プールなどの他のメモリ コンシューマーで使用できるメモリが少なくなると、パフォーマンスが低下する可能性があります。
注釈
optimize for ad hoc workloads オプションを1に設定すると、新しいプランのみが影響を受けます。プラン キャッシュに既に存在するプランは影響を受けません。
既にキャッシュされているクエリ プランにすぐに適用するには、ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE を使ってプラン キャッシュをクリアするか、または SQL Server を再起動する必要があります。
推奨事項
プラン キャッシュに 1 回のみ使われるプランを多数格納することは避けてください。 一般的な原因には、次のようなものがあります。
一貫して定義されていないクエリ パラメーターのデータ型。 これは文字列の長さに特に適用されますが、最大長、有効桁数、小数点以下桁数が含まれるすべてのデータ型に適用できます。 たとえば、
@Greetingという名前のパラメーターが 1 回の呼び出しで nvarchar(10) として渡され、次の呼び出しで nvarchar(20) として渡される場合、パラメーター サイズごとに別のプランが作成されます。パラメーター化されていないクエリ。 ハードコーディングされた値がデータベース エンジンに送信されるパラメーターがクエリに 1 つ以上ある場合、クエリごとに多数のクエリ プランが存在する可能性があります。 プランは使用されているクエリ パラメーターのデータ型と長さの組み合わせごとに存在する可能性があります。
1 回しか使用されないプランの数により、OLTP サーバーの SQL Server データベース エンジンのメモリの大部分が占有されていて、これらのプランがアドホック プランである場合は、このサーバー オプションを使って、これらのオブジェクトのメモリ使用量を削減します。
optimize for ad hoc workloads オプションが有効になっている場合、プラン スタブのみがキャッシュされるため、単一使用クエリの実行プランを表示できません。 環境とワークロードによっては、次の 2 つの機能を利用できます。
SQL Server 2016 (13.x) で導入されたクエリ ストア機能は、クエリ プランの変更によるパフォーマンスの違いをすばやく見つけるのに役立ちます。 クエリ ストアは、SQL Server 2022 (16.x) 以降のバージョンの新しいデータベースで既定で有効になっています。
強制パラメーター化を行うと、クエリをコンパイルおよび再コンパイルする頻度を緩和できるので、データベースによってはパフォーマンスが向上します。 一般的に POS (point-of-sale) などのアプリケーションから大量のクエリが同時に実行されるデータベースは、強制パラメーター化によりパフォーマンスが向上します。
強制パラメーター化では、パラメーターの機密度が原因でパフォーマンスの問題が発生する可能性があります。 詳細については、「パラメーターに依存する問題の調査と解決」を参照してください。 SQL Server 2022 (16.x) 以降のバージョンでは、パラメーターに依存するプランの最適化を有効にすることもできます。
例
1 回のみ使われてキャッシュされるプランの数を調べるには、次のクエリを実行します。
SELECT objtype,
cacheobjtype,
SUM(refcounts) AS AllRefObjects,
SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
AND usecounts = 1
GROUP BY objtype, cacheobjtype;