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

» 2012年02月23日 00時00分公開

[熊澤幸生，株式会社 CSK Winテクノロジ]

頻繁に使うクエリを探してチューニング

　ここまで説明してきたように、SQL Serverに本来の性能を発揮させるには、プロシージャキャッシュの分析が大切です。プロシージャキャッシュが活用できていないという問題は、SQL Server 2000から、2005、2008に移行するときに発覚することが多いです。SQL Server 2000までは、プロシージャキャッシュの内容を分析するデータを取ることが難しかったからです。

　SQL Server 2000のころに、プロシージャキャッシュのデータを取るためには、Quest SoftwareのSpotlight on SQL Serverというツールを使うしかありませんでした。しかし、SQL Server 2005からは、動的管理ビュー（DMV：Dynamic Management View）で、プロシージャキャッシュの内容を見ることができるようになりました。

　図1は、プロシージャキャッシュの内容を動的管理ビューで取得した結果です。クエリごとに、呼び出しを受けた回数やキャッシュ内で占める大きさ、コンパイル済みであるかどうかということなどが分かるようになっています。どのクエリをピンポイントでチューニングすると性能が上がるかということが分かるのです。

図1　動的管理ビューから取得した、プロシージャキャッシュの内容。クリックすると拡大

　この図を見ると、頻繁に使うクエリがはっきりします。頻繁に使うクエリは、実行プランを調べて徹底的にチューニングして、パラメータ化を図り、可能ならばストアドプロシージャにしていく。この過程を繰り返すことで、データベースアプリケーションの処理性能は確実に上がります。

　SQL Server 2008からは、アドホッククエリによるプロシージャキャッシュの浪費を避けるために、新たな初期設定パラメータが利用できるようになりました。sp_configureストアドプロシージャに以下のように引数を与えて実行してください。

sp_configure 'optimize for ad hoc workloads', '1';

　こうするとSQL Serverは、アドホッククエリの初回実行時は、クエリのコンパイル結果ではなく、小さなスタブをプロシージャキャッシュに残します。再び同じクエリを実行することになったら、クエリをコンパイルし、完全なコンパイル結果をプロシージャキャッシュに保存します。

インデックスを活用できているか調べよう

　クエリをパラメータ化クエリに変えていくときは、そのクエリが効率の良いものかどうかを考えることも必要です。パラメータ化クエリに変えたとしても、そのクエリの実行プランを見るとあまり効率が良くないということでは、最高の性能は期待できません。つまり、クエリをパラメータ化クエリにする前に、実行プランをチェックして、チューニングする必要があるということです。

　クエリチューニングと聞くと、難解で手間のかかるものという印象をお持ちの方もいらっしゃるでしょう。最初は簡単なところから見直してください。表のデータを結合（Join）するときに、不要な列（カラム）のデータまで取り出していませんか？これは、確実にメモリを浪費する原因になります。本当に必要なデータだけを取り出すように、クエリを見直してください。

　そしてもう1つ、インデックスの使い方に注目しましょう。さらに、書き込み性能の低下を許容して、読み取り性能を上げる「テーブル設計の正規化崩し」という手段もありますが、ここではインデックスの扱い方について解説します。

　SQL Server Management Studioでクエリの実行プランを調べると、データを検索している部分に「Index Seek」「Index Scan」「Table Scan」といった記述があります。Index Seekは、Bツリーインデックスをルートから順にたどって、最短の手順でデータを探し出していることを意味します。インデックスを活用して、最も効率良くデータを探索しているということです。

　一方、Index ScanやTable Scanと出てくる部分は、インデックスを有効活用できていません。Index Scanはインデックスのリーフノードを順に調べて、目的のデータを探していることを指します。一応インデックスを使っていますが、最悪の場合はリーフノードをすべて検索することになります。

　Table Scanは最悪の方法でデータを検索していることを意味します。テーブルのデータを順に調べて、目的のデータを探しているのです。インデックスをまるで活用できていません。

なるべくIndex Seekになるように

　クエリをチューニングするときは、的確にインデックスを設定し、Index Seekでデータを検索させるようにすることを目標とします。インデックスを設定していても、その方法が悪いとIndex Scanや、Table Scanになってしまいます。

　また、同じ結果を得るにしても、クエリの書き方を変えるだけでインデックスの使い方が変わることもあります。例えば図2は、Ordersというテーブルから、orderid、custid、empid、shipperid、orderdate、filterという6種類の列のデータを取り出すクエリの実行プランです。このクエリでは、WHERE句でorderdate列にある特定の値を指定して、その行だけを取り出そうとしています。

図2　クエリの実行プランの例。この例ではインデックスを活用できていない。クリックすると拡大

　実行プランをよく見ると、右上にIndex Seekという部分がありますが、コストが0％になっています。これは、Index Seekが働いていないということを意味します。そして、中央の下にあるRID Lookupというところのコストが100％になっています。この手順だけで目的のデータを探しているということを読み取れます。

　RID Lookupとは、RID（行識別子）という、テーブルの行を識別するIDを先頭から調べて、目的のデータを探しているということを意味します。効率が良いとはとても言えません。

　さらに、図2の上部を見ると、緑色の文字で「不足しているインデックス」というメッセージが出ているのが見えます。このメッセージによると、orderdate列にインデックスを張ると良いとあります。

　次に、図3を見てください。これも、図2と同じテーブルから同じようなデータを取り出すクエリの実行プランを示したものです。ただし、行の絞り込み方を少し変えています。orderid列にある特定の値を指定しています。

図3　クエリの実行プランの例。インデックスを活用できている例。クリックすると拡大

　これを見ると、右上のIndex Seekを活用できていることが分かります。NonClusteredという記述から、非クラスタ化インデックスを利用していることも分かります。非クラスタ化インデックスをルートからたどって、最短の手順で目的の行のRIDを取得しています。インデックスを張った方が良いというメッセージもありません。

　RIDを取得したら、RID Lookupで目的の行を取得しています。この場合は、すでに目的の行のRIDを非クラスタ化インデックスを利用して取得しているので、すべてのRIDを調べる必要はありません。その分、図2でお見せした実行プランよりも効率が良いと言えます。

　最後に、図4をご覧ください。図2、図3と同じテーブルから同じようなデータを取り出すクエリの実行計画です。データを絞り込むWHERE句では、orderdate列の値を対象に、一定の範囲を設定して、その範囲に入る行を取り出そうとしています。

図4　クエリの実行プランの例。テーブルのデータを片っ端から調べる最悪のプラン。クリックすると拡大