optimize for ad hoc workloads サーバー構成オプション

適用対象:yesSQL Server (サポートされているすべてのバージョン) Yes Azure SQL Database Yes Azure SQL Managed Instance yes Analytics Platform System (PDW)

optimize for ad hoc workloads オプションを使用すると、1 回のみ使用するアドホック バッチが多数含まれているワークロードのプラン キャッシュの効率を高めることができます。 このオプションを 1 に設定すると、データベース エンジンでは、バッチが初めてコンパイルされるときに、完全なコンパイル済みプランではなく、コンパイル済みプランの小さいスタブがプラン キャッシュに格納されます。 これにより、再利用されないコンパイル済みプランでプラン キャッシュがいっぱいにならないようにして、メモリの負荷を下げることができます。

コンパイルされたプランスタブを使用するとデータベースエンジン、このアドホックバッチがコンパイル済みであることを認識できますが、コンパイルされたプランスタブだけが格納されるため、このバッチが再度呼び出される (コンパイルまたは実行される) と、データベースエンジンはバッチをコンパイルし、コンパイルされたプランのスタブをプランキャッシュに追加します。

コンパイル済みプランのスタブは、sys.dm_exec_cached_plans カタログ ビューによって表示される cacheobjtype の 1 つです。 これには、一意の SQL ハンドルとプラン ハンドルが含まれています。 コンパイル済みプランのスタブには、関連付けられた実行プランがないため、プラン ハンドルに対してクエリを実行しても、XML プラン表示は返されません。

トレースフラグ 8032は、キャッシュ制限パラメーターを SQL Server 2005 (1.x) RTM 設定に戻します。これにより、一般にキャッシュのサイズを大きくすることができます。 この設定は、頻繁に再利用されるキャッシュ エントリがキャッシュ内に収まりきれない場合、および optimize for ad hoc workloads Server Configuration Option によってプラン キャッシュに関する問題を解決できなかった場合に使用します。

警告

トレース フラグ 8032 を使用した場合、キャッシュが大きいために他のメモリ コンシューマー (バッファー プールなど) で利用できるメモリが少なくなると、パフォーマンスが低下することがあります。

推奨事項

プラン キャッシュに 1 回のみ使われるプランを多数格納することは避けてください。 この問題の一般的な原因は、クエリ パラメーターのデータ型が一貫して定義されていないことです。 これは文字列の長さに特に適用されますが、最大長、有効桁数、小数点以下桁数が含まれるすべてのデータ型に適用できます。 たとえば、@Greeting という名前のパラメーターが 1 回の呼び出しで nvarchar(10) として渡され、次の呼び出しで nvarchar(20) として渡される場合、パラメーター サイズごとに別のプランが作成されます。 クエリに複数のパラメーターが含まれ、これらが呼び出し時に一貫して定義されていないと、クエリごとに大量のクエリ プランが存在している場合があります。 プランは使用されているクエリ パラメーターのデータ型と長さの組み合わせごとに存在する可能性があります。

単一使用プランの数が OLTP サーバーの SQL Server データベースエンジンメモリの大部分を占めていて、これらのプランがアドホックプランである場合は、このサーバーオプションを使用して、これらのオブジェクトによるメモリ使用量を減らすことができます。 1 回のみ使われてキャッシュされるプランの数を調べるには、次のクエリを実行します。

SELECT objtype, cacheobjtype, 
  AVG(usecounts) AS Avg_UseCount, 
  SUM(refcounts) AS AllRefObjects, 
  SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1
GROUP BY objtype, cacheobjtype;

重要

optimize for ad hoc workloads を 1 に設定すると、新しいプランのみに影響します。プラン キャッシュ内の既存のプランには影響しません。 既にキャッシュされているクエリプランにすぐに影響を与えるには、 ALTER database スコープ構成のクリア PROCEDURE_CACHEを使用してプランキャッシュをクリアするか、SQL Server を再起動する必要があります。

参照