キャッシュを無駄遣いしないようにクエリを書く：真・Dr. K's SQL Serverチューニング研修（6）（1/3 ページ）

プロシージャキャッシュを最大限活用するには

　第5回で、サーバに搭載すべきメモリの量を見積もる方法を解説しました。SQL Serverの主流が32bit版から64bit版に変わって、広大なメモリ空間を使えるようになったのは良いのですが、64bit版のSQL Serverでは最大2Tbytesのメモリ空間を、さまざまなキャッシュで共有することになります。キャッシュの種類と、それぞれがどんなデータをキャッシュするのか意識することが大切です。

　そして、キャッシュの中でもプロシージャキャッシュは、サーバが搭載しているメモリ容量によって、大きさが変動するということをお話ししました。32bit版SQL Serverでは、プロシージャキャッシュは常に最大0.5Gbytes程度でしたが、64bit版では、プロシージャキャッシュの容量は全体的に大きくなった上、サーバが搭載するメモリ容量に比例して大きくなっていくように変わりました。

　特にSQL Server 2000以前の環境で開発したアプリケーションを、SQL Server 2005以降の64bit版に移行する場合は、プロシージャキャッシュ領域が肥大化するので注意が必要です。

　今回は、このプロシージャキャッシュ領域を無駄なくメモリオブジェクト上で利用するには、どのようなクエリを書かなければならないかということについて解説します。もう少し詳しく言うと、プロシージャキャッシュの内容を極力再利用させて、SQL Serverの処理性能を発揮させる方法について説明します。

アドホッククエリがプロシージャキャッシュを食いつぶす

　前回、プロシージャキャッシュには、大きく分けてオブジェクトプランとSQLプランの2種類のデータが入ると説明しました。前者は、ストアドプロシージャや関数、トリガーなど、再利用を前提にして開発者が作成したクエリをコンパイルしたものを指します。

　後者は、SQL Serverがクライアントから受け取ったT-SQL文をコンパイルしたものを指します。最初に気を付けたいのは、このSQLプランの領域の扱いです。

　クエリの中でも、その場限りで使い捨てのクエリをアドホッククエリと呼ぶことは前回までに説明しました。アドホッククエリに限らず、クエリをSQL Serverが実行するときは、オプティマイザが最適な実行プランにブレークダウンした後、コンパイルした実行ステップごとに実行します。

　アドホッククエリは使い捨てのクエリですので、クエリのコンパイル結果をキャッシュに残す必要はほとんどありません。しかし、SQL Serverはアドホッククエリのコンパイル結果をプロシージャキャッシュに蓄積します。まったく同じクエリを繰り返し実行することはなくても、蓄積しておけば、後で同じクエリを実行することになるかもしれません。そのときに役立つという考えからでしょう。

　しかし、アドホッククエリが頻発すると、プロシージャキャッシュにアドホッククエリのコンパイル結果がどんどんたまっていき、領域を圧迫するようになります。いつ使うか分からないクエリのコンパイル結果を山ほどためることは、効率の良いやり方とは言えません。

　SQL Serverは、使用しないキャッシュデータを一掃するガベージコレクションの機能も備えていますが、この機能は蓄積したキャッシュデータの容量が一定値を超えたら非同期で動作します。そのしきい値はサーバが搭載する物理メモリの容量で決まります。

　そして、アドホッククエリが頻発するということは、クエリのコンパイル処理が何度も発生するということも意味します。クエリをコンパイルすると、もちろんプロセッサ時間を消費します。複雑なものになれば、消費する時間も長くなります。

アドホッククエリではなくパラメータ化クエリを

　ここまで説明してきたことを考えると、アドホッククエリを頻発させることは、SQL Serverの性能には良い影響を与えません。プロシージャキャッシュを有効活用することを考えると、アプリケーションにはアドホッククエリはなるべく発行させないようにするべきと言えます。

　では、どうすれば良いのか？ SQL Serverにはパラメータ化クエリというものを扱う機能があります。SQL Serverは、パラメータ化クエリを実行すると、クエリの実行のたびに変動するパラメータを、変数のように扱って、コンパイルします。パラメータを変えただけのクエリを実行するときは、コンパイル済みのクエリにパラメータを与えて実行することができるのです。この機能を利用すればコンパイル済みのクエリが不必要に増えることもありませんし、プロセッサ時間を浪費することもありません。

　つまり、SQL Serverのプロシージャキャッシュを上手に活用し、高い処理性能を発揮させるには、アドホッククエリをつぶしていき、パラメータ化クエリを活用することが大変重要なポイントと言えます。

クエリの書き方を統一する

　アドホッククエリを排除して、パラメータ化クエリを活用するにはまず、プログラムが発行するクエリに注目しましょう。プログラムが動的に作ったクエリや、プログラムに埋め込んだクエリは、一般にアドホッククエリになりやすいからです。

　プログラムが動的に発行するクエリは、SQL Serverのexecute命令に引数として渡すことが普通です。こうすると、SQL ServerがSQL文を実行してくれます。

　ここで、sp_executesqlというストアドプロシージャを使うようにしてください。このストアドプロシージャは、クエリを引数に与えて実行すると、クエリをパラメータ化クエリにしてくれるのです。

　このとき、1つ注意していただきたいことがあります。それは、変動するパラメータを除いた部分は、すべて表記を統一しなければならないということです。テーブルなどのオブジェクトを指定する部分や、変数のデータ長の記述に気を付けてください。オブジェクトの指定には、完全修飾名（サーバ名、データベース名、スキーマ名、オブジェクト名すべてを指定）を使うのか、オブジェクト名だけで済ませるのかどちらかに決めてください。

　クエリの表記を統一できていないと、表記の異なるクエリの数だけ、パラメータ化クエリができてしまいます。同じ処理をするクエリをいくつもキャッシュに蓄積するのは、プロシージャキャッシュの無駄遣いです。