1 月に開催された Get The Fact セミナーで optimize for ad hoc workloads オプション についてのお話があったようですので今回はこの内容についてまとめてみたいと思います。
■optimize for ad hoc workloads を有効にした時の効果
optimize for ad hoc workloads オプションは SQL Server 2008 から追加された機能となります。
以下のようなアドホッククエリを実行したとします。
SELECT * FROM dbo.Table_1 WHERE 1 = 0 |
この時、プロシージャキャッシュには以下のようにキャッシュが行われます。
SELECT |
Profiler でクエリのコンパイルを確認するとアドホッククエリの初回実行時にコンパイルが発生していることが確認できます。
# 複数回同一のアドホッククエリを実行していますが、[Query Compile] の発生は初回のみとなっています。
それでは、[optimize for ad hoc workloads] を有効にしてプロシージャキャッシュの内容を確認してみたいと思います。
sp_configure ‘optimize for ad hoc workloads’,1 |
一度 [DBCC FREEPROCCACHE] でプロシージャキャッシュの内容をクリアしてから、アドホッククエリを実行してプロシージャキャッシュの内容を確認してみます。
先ほどとは少し違った形でキャッシュされていることが確認できると思います。
具体的に書きますと、
- size_in_bytes が設定前と比べて小さくなっている
- cacheobjtype が [Compiled Plan Stub] となっている
- query_plan が NULL となっている
ということが確認できます。
これが、[optimize for ad hoc workloads] を設定したとこによる効果になります。
optimize for ad hoc workloads を有効にするとアドホッククエリの初回実行時には、コンパイル済みプランのスタブがキャッシュされます。
アドホッククエリの初回実行時に完全なコンパイル済みのプランではなくスタブをキャッシュすることで、アドホッククエリ実行時のプロシージャキャッシュのサイズを低減することが可能になります。
スタブのキャッシュは初回のみになりますので、2 回目の実行時には完全なプランがキャッシュされます。
■コンパイル回数の増加
optimize for ad hoc workloads を有効にすると初回実行時にはスタブがキャッシュされメモリの使用量を抑えることができるようになります。
それならば毎回設定しておけばよいのでは?ということも考えられるのですが、optimize for ad hoc workloads を有効にするとコンパイル回数が増加します。
optimize for ad hoc workloads 有効時には、
- 初回実行時にはコンパイル済みプランのスタブをキャッシュ
- 2 回目の実行時に完全なコンパイル済みプランをキャッシュ
という挙動になります。
そのため、アドホッククエリを複数回実行した場合は 2 回コンパイルが発生することになります。
こちらが、optimize for ad hoc workloads を有効にして、複数回同一のアドホッククエリを実行した場合のコンパイル発生状況になります。
コンパイルが 2 回発生しているのが確認できますね。
optimize for ad hoc workloads が無効な状態で複数回同一のアドホッククエリを実行した場合のコンパイル発生状況がこちらになります。
optimize for ad hoc workloads が無効な場合 (デフォルトの設定) は初回の実行時に完全なコンパイル済みプランをキャッシュしますので、コンパイルは一度しか発生しないことになります。
ストアドプロシージャ、パラメーター化クエリで最初から実行するのがベストだとは思いますが、大量のアドホッククエリでプロシージャキャッシュがバッファキャッシュを圧迫している場合は有益な設定となりそうですね。
コンパイルの回数は増えるので CPU リソースの使用状況には要注意だと思いますが。
[…] by optimize for ad hoc workloads http://blog.engineer-memo.com/2011/02/14/optimize-for-ad-hoc-workloads-%E3%81%AB%E3%82%88%E3%82%8B%E3%82%B9%E3%82%BF%E3%83%96%E3%81%AE%E3%82%AD%E3%83%A3%E3%83%83%E3%82%B7%E3%83%A5%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6/ […]
Plan Cache – FAQ – 1 | Learning in the Open
20 5月 16 at 03:01