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

適用対象:○SQL Server XAzure SQL DatabaseXAzure SQL Data Warehouse XParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

optimize for ad hoc workloads オプションを使用すると、1 回のみ使用するアドホック バッチが多数含まれているワークロードのプラン キャッシュの効率を高めることができます。The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. このオプションを 1 に設定すると、 データベース エンジンDatabase Engine では、バッチが初めてコンパイルされるときに、完全なコンパイル済みプランではなく、コンパイル済みプランの小さいスタブをプラン キャッシュに格納します。When this option is set to 1, the データベース エンジンDatabase Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. これにより、再利用されないコンパイル済みプランでプラン キャッシュがいっぱいにならないようにして、メモリの負荷を下げることができます。This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

コンパイル済みプランのスタブを使用すると、 データベース エンジンDatabase Engine は、このアドホック バッチが既にコンパイルされているが、コンパイル済みプランのスタブしか格納していないと認識します。そのため、このバッチが再度呼び出される (コンパイルまたは実行される) と、 データベース エンジンDatabase Engine では、バッチがコンパイルされ、コンパイル済みプランのスタブがプラン キャッシュから削除され、完全なコンパイル済みプランがプラン キャッシュに追加されます。The compiled plan stub allows the データベース エンジンDatabase Engine to recognize that this ad hoc batch has been compiled before but has only stored a compiled plan stub, so when this batch is invoked (compiled or executed) again, the データベース エンジンDatabase Engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache.

コンパイル済みプランのスタブは、sys.dm_exec_cached_plans カタログ ビューによって表示される cacheobjtype の 1 つです。The compiled plan stub is one of the cacheobjtypes displayed by the sys.dm_exec_cached_plans catalog view. これには、一意の SQL ハンドルとプラン ハンドルが含まれています。It has a unique sql handle and plan handle. コンパイル済みプランのスタブには、関連付けられた実行プランがないため、プラン ハンドルに対してクエリを実行しても、XML プラン表示は返されません。The compiled plan stub does not have an execution plan associated with it and querying for the plan handle will not return an XML Showplan.

トレース フラグ 8032 は、キャッシュ制限パラメーターを SQL Server 2005SQL Server 2005 RTM の設定に戻します。これにより、一般に、より大きいキャッシュに対応できるようになります。Trace flag 8032 reverts the cache limit parameters to the SQL Server 2005SQL Server 2005 RTM setting which in general allows caches to be larger. この設定は、頻繁に再利用されるキャッシュ エントリがキャッシュに収まらない場合や、サーバー構成オプション [アドホック ワークロードの最適化] でプラン キャッシュの問題を解決できない場合に使用します。Use this setting when frequently reused cache entries do not fit into the cache and when the optimize for ad hoc workloads Server Configuration Option has failed to resolve the problem with plan cache.

警告

トレース フラグ 8032 を使用した場合、キャッシュが大きいために他のメモリ コンシューマー (バッファー プールなど) で利用できるメモリが少なくなると、パフォーマンスが低下することがあります。Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool.

推奨事項Recommendations

プラン キャッシュに 1 回のみ使われるプランを多数格納することは避けてください。Avoid having a large number of single-use plans in the plan cache. この問題の一般的な原因は、クエリ パラメーターのデータ型が一貫して定義されていないことです。A common cause of this problem is when the data types of query parameters is not consistently defined. これは文字列の長さに特に適用されますが、最大長、有効桁数、小数点以下桁数が含まれるすべてのデータ型に適用できます。This particularly applies to the length of strings but can apply to any data type that has a maxlength, a precision, or a scale. たとえば、@Greeting という名前のパラメーターが 1 回の呼び出しで nvarchar(10) として渡され、次の呼び出しで nvarchar(20) として渡される場合、パラメーター サイズごとに別のプランが作成されます。For example, if a parameter named @Greeting is passed as an nvarchar(10) on one call and an nvarchar(20) on the next call, separate plans are created for each parameter size. クエリに複数のパラメーターが含まれ、これらが呼び出し時に一貫して定義されていないと、クエリごとに大量のクエリ プランが存在している場合があります。If a query has several parameters and they are not consistently defined when called, a large number of query plans could exist for each query. プランは使用されているクエリ パラメーターのデータ型と長さの組み合わせごとに存在する可能性があります。Plans could exist for each combination of query parameter data types and lengths that have been used.

多数の 1 回のみ使われるプランにより、OLTP サーバーの SQL Server データベース エンジンSQL Server Database Engineのメモリの多くの部分が占有されていて、これらのプランがアドホック プランである場合は、このサーバー オプションを使って、これらのオブジェクトのメモリ使用量を削減します。If the number of single-use plans take a significant portion of SQL Server データベース エンジンSQL Server Database Engine memory in an OLTP server, and these plans are Ad-hoc plans, use this server option to decrease memory usage with these objects. 1 回のみ使われてキャッシュされるプランの数を調べるには、次のクエリを実行します。To find the number of single-use cached plans, run the following query:

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 に設定すると、新しいプランのみに影響します。プラン キャッシュ内の既存のプランには影響しません。Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected. 既にキャッシュされているクエリ プランにすぐに適用するには、ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE を使ってプラン キャッシュをクリアするか、または SQL ServerSQL Server を再起動する必要があります。To affect already cached query plans immediately, the plan cache needs to be cleared using ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, or SQL ServerSQL Server has to restart.

参照See Also

sys.dm_exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
サーバー構成オプション (SQL Server)Server Configuration Options (SQL Server)