SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

optimize for ad hoc workloads によるスタブのキャッシュについて

with one comment

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
    refcounts,
    usecounts,
    size_in_bytes,
    cacheobjtype,
    objtype,
    text,
    query_plan
FROM
    sys.dm_exec_cached_plans
    CROSS APPLY
    sys.dm_exec_sql_text(plan_handle) AS dest
    CROSS APPLY
    sys.dm_exec_query_plan(plan_handle)
WHERE
    dest.dbid = DB_ID(N’TEST’)

image
Profiler でクエリのコンパイルを確認するとアドホッククエリの初回実行時にコンパイルが発生していることが確認できます。
# 複数回同一のアドホッククエリを実行していますが、[Query Compile] の発生は初回のみとなっています。
image

それでは、[optimize for ad hoc workloads] を有効にしてプロシージャキャッシュの内容を確認してみたいと思います。

sp_configure ‘optimize for ad hoc workloads’,1
RECONFIGURE

一度 [DBCC FREEPROCCACHE] でプロシージャキャッシュの内容をクリアしてから、アドホッククエリを実行してプロシージャキャッシュの内容を確認してみます。
image

先ほどとは少し違った形でキャッシュされていることが確認できると思います。

具体的に書きますと、

  • size_in_bytes が設定前と比べて小さくなっている
  • cacheobjtype が [Compiled Plan Stub] となっている
  • query_plan が NULL となっている

ということが確認できます。

これが、[optimize for ad hoc workloads] を設定したとこによる効果になります。

optimize for ad hoc workloads を有効にするとアドホッククエリの初回実行時には、コンパイル済みプランのスタブがキャッシュされます。
アドホッククエリの初回実行時に完全なコンパイル済みのプランではなくスタブをキャッシュすることで、アドホッククエリ実行時のプロシージャキャッシュのサイズを低減することが可能になります。

スタブのキャッシュは初回のみになりますので、2 回目の実行時には完全なプランがキャッシュされます。
image

 

■コンパイル回数の増加


optimize for ad hoc workloads を有効にすると初回実行時にはスタブがキャッシュされメモリの使用量を抑えることができるようになります。

それならば毎回設定しておけばよいのでは?ということも考えられるのですが、optimize for ad hoc workloads を有効にするとコンパイル回数が増加します。

optimize for ad hoc workloads 有効時には、

  1. 初回実行時にはコンパイル済みプランのスタブをキャッシュ
  2. 2 回目の実行時に完全なコンパイル済みプランをキャッシュ

という挙動になります。

そのため、アドホッククエリを複数回実行した場合は 2 回コンパイルが発生することになります。
こちらが、optimize for ad hoc workloads を有効にして、複数回同一のアドホッククエリを実行した場合のコンパイル発生状況になります。
image

コンパイルが 2 回発生しているのが確認できますね。

optimize for ad hoc workloads が無効な状態で複数回同一のアドホッククエリを実行した場合のコンパイル発生状況がこちらになります。
image
optimize for ad hoc workloads が無効な場合 (デフォルトの設定) は初回の実行時に完全なコンパイル済みプランをキャッシュしますので、コンパイルは一度しか発生しないことになります。

 

ストアドプロシージャ、パラメーター化クエリで最初から実行するのがベストだとは思いますが、大量のアドホッククエリでプロシージャキャッシュがバッファキャッシュを圧迫している場合は有益な設定となりそうですね。
コンパイルの回数は増えるので CPU リソースの使用状況には要注意だと思いますが。

Written by Masayuki.Ozawa

2月 14th, 2011 at 10:08 pm

Posted in SQL Server

Tagged with

One Response to 'optimize for ad hoc workloads によるスタブのキャッシュについて'

Subscribe to comments with RSS or TrackBack to 'optimize for ad hoc workloads によるスタブのキャッシュについて'.

Leave a Reply