実行プランを読む - 活用編 (その 1) - クエリチューニング

  • Article

神谷 雅紀
Esclation Engineer

 

前回までの内容で、実行プランからクエリがどのように実行されるのかを知ることができるかと思います。では、次に、実行プランを活用して、クエリの無駄を省き、チューニングを行う方法を考えてみましょう。

本題に入る前に。どのクエリをチューニング対象にするか、クエリのどの部分をチューニング対象とするかを決定する場合は、アムダールの法則を考慮して、掛かるコストと効果を見極めながら、慎重に決定することをお勧めします。

 

Key Lookup (Clustered Index Seek Lookup) や RID Lookup を排除する

 

次の実行プランには、Key Lookup が含まれています。

keylookup

SET SHOWPLAN_ALL などの regacy プラン表示では、下のように Lookup 句付き Clustered Index Seek が含まれます。これは、Key Lookup と同意です。

regacy

このクエリは、非クラスタ化インデックスで行を絞り込み、その後、絞り込んだキーを使ってクラスタ化インデックスを検索することで、結果を生成しています。もし、このテーブルにクラスタ化インデックスがなければ、Key Lookup の代わりに RID Lookup が行われます。

では、このクエリをチューニングする、言い換えれば、実行時間を短くするためには何をすべきでしょうか？

クエリをチューニングすることは、基本的に、クエリによって行われる読み取り数を減らすことであると考えて問題ないと思います。

では、読み取り数を減らすためには何ができるでしょうか？

読み取り数を減らすひとつの方法として、Key Lookup をクエリから排除する方法が考えられます。そのために、まずは、なぜ Key Lookup (Clustered Index Seek) が行われているのかを見てみましょう。

以下は、このクエリの実行プランの一部として示される DefinedValues 列と OutputList 列です。

definedvalue

DefinedValues 列はオペレータが生成する列を示し、OutputList 列はオペレータが出力する列を示しています。上の例では、Index Seek は c1 列を生成し、c1 列を出力しています。一方、Nested Loops は行の結合を行うオペレータであるため、新しい列は生成しませんが、子オペレータである Index Seek と Clustered Index Seek から受け取った列 c1, c2, c3 を出力しています。

ここから、Key Lookup が行われている理由は、DefinedValues に列挙されている列 c2, c3 を得るためであることが分かります。つまりは、Key Lookup を排除するためには、Index Seek が参照しているインデックスが列 c2, c3 を含むようにしてあげればいいということになります。

クエリが必要とするすべての列を含む非クラスタ化インデックスは、カバーリングインデックス (covering index) と呼ばれます。カバーリングインデックスにより、テーブルのデータ部 (ヒープやクラスタ化インデックス) を参照する必要がなくなるため、読み取るページ数が減ると同時に、処理対象となる行数が減ることで、クエリパフォーマンスの向上が期待できます。

カバーリングインデックスについては、WebCast SQL パフォーマンス チューニング : 中上級編 vol. 1 - カバーリングインデックス/クエリヒントの利用 (前編) および SQL パフォーマンス チューニング : 中上級編 vol. 1 - カバーリングインデックス/クエリヒントの利用 (後編) でも触れています。

以下は、クエリが対象としていたインデックスの定義です。

create index ind_keylookup on keylookup (c5) go

少し話題がずれますが、このインデックスを構成している列は c5 のみであるにも関わらず、このインデックスに対する Index Seek の DefinedValues 列に、インデックスキーに含まれていない c1 が示されているのはなぜでしょうか？それは、このテーブルにはクラスタ化インデックスがあり、クラスタ化インデックスキーに c1 が含まれているためです。非クラスタ化インデックスは、クラスタ化インデックスキーを必ず含みます。

話題を元に戻します。非クラスタ化インデックス ind_keylookup に列 c2, c3 を含める方法としては、2 つの方法があります。1 つはインデックスキーとして追加する方法、もう 1 つは INCLUDE 列として追加する方法です。

インデックスキーとして追加した場合には、インデックスは当然 c2, c3 も含めてキー順に並べられます。c2 や c3 を更新すれば、列値の変更だけでなく、変更後の値に応じてインデックスレコードの物理的な位置も変更されます。一方、INCLUDE 列として追加した場合には、c2, c3 の値を変更しても、インデックスレコードの位置を変更する必要はありません。このような違いがあるため、c2, c3 が頻繁に更新される可能性がある場合や c2, c3 を検索条件として指定することがない場合などは、INCLUDE 列として追加した方が、更新時のオーバーヘッドを減らすことができ、全体としてのメリットは大きくなります。

c2 ,c3 列を追加するために、以下を実行します。

create index ind_keylookup on keylookup (c5)  include (c2,c3) with drop_existing go

新しいインデックスでの実行プランは以下です。非クラスタ化インデックスに対する Index Seek のみの非常にシンプルな実行プランになりました。

planafter

※ テスト用テーブル名に keylookup という名前を付けてしまったため、実行プランの中に keylookup という文字列がありますが、これは Key Lookup オペレータではなくテーブル名です。念のため。

効果を見るために、インデックス変更前の I/O 数とインデックス変更後の I/O 数 (SET STATISTICS IO の結果) を比較してみます。

  • 元の状態でクエリを実行した場合の I/O

テーブル 'keylookup'。スキャン回数 1、論理読み取り数 3133、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

  • INCLUDE 列として c2, c3 を追加した状態でクエリを実行した場合の I/O

テーブル 'keylookup'。スキャン回数 1、論理読み取り数 271、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

サンプルテーブルとサンプルデータでの結果であるため、作為的に上の結果のような差を出すことは簡単で、上の値はあまり参考にはなりませんが、少なくとも、この方法により読み取り数を減らすことはできるはずなので、試してみる価値はあるかと思います。

ちなみに、この方法を実施する上で、チューニング対象となるクエリが何の目的でどのシステムから実行されているのかなどの詳しい知識は必要ありません。実行プランとインデックスの定義だけがあればこの方法を実践することができるため、実行されているアプリケーションに関する知識がなくても、トレースを採取したり、データベーススキーマを参照変更する権限があれば、この手順は実施することができます。

尚、トレースを採取する場合は、この手順を実施した後の効果を計るために必要となるクエリ文字列とチューニングのために必要となる実行プランを採取すれば十分ですので、SQL:BatchCompleted, SQL:StmtCompleted, RPC:Completed, SP:StmtCompleted など実行されているクエリが分かるイベントに加えて、Showplan All, Showplan XML, Showplan Statistics Profile, Showplan XML Statistics Profile イベントのいずれかを含むトレースを採取します。トレースの採取方法は、SQL トレーススクリプトの作成、実行 (SQL Server 2005, 2008, 2008 R2) を参照して下さい。