64ビット時代の「バランスド・システム」：真・Dr. K's SQL Serverチューニング研修（5）（2/3 ページ）

プロシージャキャッシュは搭載メモリ容量によって変動

　プロシージャキャッシュに入るデータの中で、エンジニアが注意しなければいけないのはアドホッククエリの実行プランです。アドホッククエリはその場限りのクエリであり、繰り返し何度も実行するものではありません。キャッシュに長く保存しておくことは無駄です。データベースに接続するアプリケーションの作り方がまずいと、アドホッククエリを乱発するようになってしまいます。

　先に述べたように、アドホッククエリは繰り返し使うことはほとんどありません。しかし、プロシージャキャッシュの中にはアドホッククエリの実行プランは確実にたまっていきます。これは、プロシージャキャッシュの無駄遣いです。プロシージャキャッシュを有効に使うなら、繰り返し使うパラメータ化クエリの実行プランを蓄積したいものです。SQL Serverにはトランザクション処理とは非同期に動作し、プロシージャキャッシュの中身を一掃するガベージコレクションの機能が備わっていますが、蓄積したデータがしきい値を超えるまで動作しません。

　実は、プロシージャキャッシュの容量は、SQL Serverが64ビットになって大きく変わった点の1つです。32ビットのSQL Serverでは、プロシージャキャッシュは常に最大0.5GBでした。

　64ビットになると、SQL Serverのプロシージャキャッシュの容量は全体的に大きくなり、ハードウェアが搭載するメモリ容量に比例してさらに大きくなるように変わりました。下の図は、64ビット版SQL Serverが確保するプロシージャキャッシュの大きさと、ハードウェアが搭載するメモリ容量の関係を示したものです。

図1　64ビット版SQL Serverにおける、プロシージャキャッシュの算出方法

　32ビットのSQL Serverに比べれば、プロシージャキャッシュはかなり大きくなっています。しかし、64ビットのSQL Serverを使う場面を想像してください。32ビットでは力不足と判断されたところで使うのが普通です。こういう場面では、蓄積するデータ量も大きくなりますが、トランザクション量も相当なものになります。プロシージャキャッシュが多少大きいからと放置していると、すぐにアドホッククエリの実行プランでいっぱいになり、動作が遅くなります。

　プロシージャキャッシュがアドホッククエリの実行プランであふれるのを防ぐには、動的管理ビューを使い、分析します。キャッシュしているクエリと、その実行計画がアドホックなものか、繰り返し使っているものかが分かります。そして、データベースに接続するプログラムを修正し、クエリのストアドプロシージャ化、あるいはなるべくパラメータ化クエリになるようなクエリを発行するようにしましょう。

　さらに、繰り返し実行しているストアドプロシージャは、その実行プランまで検討して、なるべく速く動作するように書き換えておきたいところです。実行頻度の高いクエリにピンポイントで手を加えるだけで、動作はかなり速くなるでしょう。