【徹底解説】SQL Serverの実行計画の読み方とクエリチューニングのポイント

SQL Serverの実行計画(Execution Plan)は、どのように読めばよいのでしょうか?右から左か、左から右か、それともコストを確認すべきでしょうか?あるいはインデックススキャン、テーブルスキャン、ルックアップといったオブジェクトに着目すべきでしょうか?本記事では、Microsoft® SQL Serverの実行計画の正しい読み方と、クエリチューニングのポイントについて詳しく解説します。

はじめに

SQL Serverは通常、良好な実行プランを自動的に生成します。しかし、生成されたプランの妥当性を検証し、非効率なプランを自ら修正できるほど賢くはありません。そのため、DBAや開発者が実行計画を読み解き、ボトルネックを特定するスキルが非常に重要になります。

SQL Serverでは、「推定実行プラン」と「実際の実行プラン」という2種類のグラフィカルな実行計画を取得できます。これらはショートカットキーのCtrl + MまたはCtrl + Lを使うか、SQL Server Management Studio(SSMS)の標準ツールバーにある「実行」アイコンの右側に配置されたアイコンから生成できます。SQL Serverには他の種類のプランも存在しますが、本記事では扱いません。

推定実行プランと実際の実行プラン

実行計画には、以下の2種類があります。

1. 推定実行プラン(Estimated Execution Plan):SQL Serverがデータ取得のために実行すると予想される処理量を見積もったものです。クエリを実行せずに取得できます。
2. 実際の実行プラン(Actual Execution Plan):Transact-SQLクエリやバッチの実行後に生成されます。そのため、実際のリソース使用状況のメトリクスや実行時の警告など、ランタイム情報が含まれます。

データ処理量に注目して読む

同じクエリでも、推定プランと実際のプランに差が生じることはあるのでしょうか?ほとんどの場合は同じですが、統計情報の変化、スキーマ関連の変更、データの変更などによって異なる場合があります。トラブルシューティングの際には、必ず実際の実行プランを確認しましょう。

実行計画を正しく読み、真の問題箇所を特定することが重要です。まずはコストではなくデータフローに注目してください。論理読み取りや物理読み取りの数値にとらわれすぎる必要はありません。本当に重要なのは、入出力(I/O)操作の回数を減らすことです。DBAであればご存じのとおり、ストレージへのアクセスはハードウェアリソースの中で最も遅いため、この負荷を最小限に抑えるべきです。

では、統計情報はどこで確認でき、実行計画には表示されるのでしょうか?答えは「はい」です。矢印(方向線)にマウスをホバーすると、レコード数とデータサイズが表示されます。また、各矢印は操作が返すデータ量に応じて太くなったり細くなったりします。次の図をご覧ください。

オブジェクトが多い場合は、各操作が処理するデータ量を俯瞰できるより良い方法が必要です。「SentryOne Plan Explorer」をダウンロードし、このツールでプランを表示すれば、全体像を簡単に把握できます。

SentryOneをダウンロードして設定したら、実行計画をこのツールで開きましょう。SentryOneには多数のビューと説明が用意されており、必要に応じて使い分けられます。データ処理量のビューを表示するには、次の画像のように「Data size in MB」オプションを選択してビューを切り替えます。目的は、全体的なデータ処理量を削減できる機会を見つけることです。

I/O負荷を軽減したい場合は、T-SQLのSET STATISTICS IO ONを使用して、クエリのI/O使用状況の全体像をつかむとよいでしょう。SSMSでクエリ実行前にこの設定を行うと、「結果」パネルの「メッセージ」タブに結果が表示されます。出力は次のようになります。

(356 row(s) affected)

Table 'Table3'. Scan count 1, logical reads 5, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.

logical reads 5という結果は、SQL Serverがメモリ上からデータを取得するために40KB(5 × 8KB)のページを読み取ったことを示しています。クエリ最適化の際に論理読み取りを無視してはいけません。本番サーバーでは、論理読み取りが物理読み取りになる可能性もあるからです。参照しているページがメモリ上にあるのかディスク上にあるのかは、事前には分かりません。目標は、累積読み取り操作の回数を減らすことです。

推定プランと実際のプランの比較

実際の実行プランが生成されたら、実行計画内の任意の操作(演算子)や論理ユニットにマウスをホバーしてみましょう。次の画像のように、Clustered Index Seek(クラスタ化インデックスシーク)やScan(スキャン)などの物理操作の種類を確認できます。ここで注目すべきは、実行回数、実際の行数、推定行数です。個々の操作のコストは見ないようにしましょう。コストは古いハードウェアを基準とした秒単位の推定値であり、正確な詳細を提供しない可能性があります。

次の画像の例では、Clustered Index Seek演算子が1回実行されて100件のレコードを取得した一方、SQL Serverは356件と推定していました。この差は、統計情報の古さやクエリのパフォーマンスに起因する可能性があります。

実行反復回数(Iterations)に注意

次の画像は、クラスタ化インデックスに対して1件のレコードを取得するために約115万4121回もの実行が推定されている例です。これは非常に重大な問題です。演算子のコストが低ければ、つい見過ごしてコストの高い他の操作ばかりに目を向けてしまうかもしれませんが、それは結果的に高くつく判断になりかねません。このような操作は、クラスタ化インデックスが存在してもクエリパフォーマンスに大きな影響を及ぼします。なお、ヒープ(Heap)の場合、同様の操作としてRIDルックアップ(Row ID Lookup)があります。

tempdb(一時データベース)の使用

推定行数と実際の行数の差も、常に意識すべきポイントです。誤った推定が行われると、クエリに割り当てられるメモリが不足する可能性があります。その場合、SQL Serverはtempdb(一時データベース)を使って処理を完了しようとします。SQL Serverによる演算子やプランの選択ミスは、クエリの実行やデータ抽出を遅くすることにつながります。次の画像の例では、実際のレコード数は同じでも推定値が異なっています。これは、統計情報が古いか欠落していることが原因と考えられます。

なお、テーブル変数には統計情報が存在しないため、新しいリリースでは再コンパイルオプションを使用しない限り、プランは常に1および1000を返します。したがって、大量のレコードを扱う場合、テーブル変数は適切な選択とは言えません。

Sort(並べ替え)演算子

ソートの影響も考慮する必要があります。Sort演算子は主に、集計(Aggregate)、マージ結合(Merge Join)、ORDER BY句で使用されます。レコード数が少ないうちは影響ありませんが、レコードが増えるごとに処理は遅くなります。可能であればソートを避け、ORDER BY句を使わないようにしましょう。どうしてもソートが必要な場合は、ソート済みのデータをアプリケーションに送るのではなく、アプリケーション側のグリッドでソートを行う方法を検討してください。

次の画像は、ソートにかかるコストを示したものです。

Spool(スプール)演算子

もう一つ注目すべき重要な演算子がSpoolです。Spoolは、隠しオブジェクトや一時オブジェクト、作業テーブルをtempdbに格納するため、低速な演算子です。また、リバインド(Rebind)やリワインド(Rewind)を引き起こす特定の演算子と組み合わさると、さらに遅くなります。SQL ServerにはEager Spool、Lazy Spool、Table/Index Spoolなど、多くの種類のスプールが存在します。SQL Serverは、中間結果セットのためにソーステーブルへ戻るよりも一時作業テーブルを参照する方が効率的だと判断した場合に、スプールを使用します。次の画像はその例です。

スプールを使用する際は、リバインドとリワインドの回数に注目することが重要です。リワインドの方がリバインドよりもコストが高くなります。例えば次の画像の例では、演算子に2674回のリワインドが表示されています。これは、データを取得するためにクエリが2674回再実行されたことを意味します。リワインドのたびに、Table SpoolからTable Scanへ戻り、各レコードを取得しています。一方、リバインドはスプールからデータを取得し、テーブルスキャンへ戻らないことを意味します。

Hash結合とNested Loop(ネステッドループ)演算子

小規模なレコードセットに対して高いパフォーマンスを発揮するため、Hash結合とNested Loopも確認すべき演算子です。しかし、大規模なレコードセットの場合や、推定プランと実際のプランの間に大きな差がある場合、これらの演算子はメモリの代わりにtempdbを使用する可能性があり、深刻な影響を及ぼします。その際、SQL Serverは演算子の詳細に「operator used tempdb to spill data during executions(実行中にtempdbへデータをスピルしました)」という警告を表示します。この警告が出た場合は、統計情報に注意を払いましょう。推定が誤っていると、ループはメモリ割り当てが不足したり、不要な繰り返し処理が続いたりします。

実行に割り当てられたメモリも確認しましょう。メモリ割り当てを表示するには、実行計画の起点(左端)を選択してプロパティボックスを開きます。異常がなければ、クエリチューニングが理想的な選択肢となります。

まとめ

クエリ最適化における最初の目標は、読み書き全体(つまりディスクI/O)を削減することです。メモリ上の読み書きである論理読み取り・論理書き込みも忘れてはいけません。I/Oを削減できれば、問題の大部分は解決し、クエリは格段に速くなります。

次に、tempdbでの活動によって高コストになっている他の操作に目を向けましょう。tempdbは多くの操作で使用され、常に高コストであることを忘れないでください。リワインド(あらゆる操作の実行回数)、スプール、ソート、ループを確認しましょう。これらはtempdbと組み合わせると特に高コストになります。また、各演算子に表示される警告の確認も忘れずに行いましょう。警告は問題解決のための貴重な手がかりとなります。

本記事では不足インデックス(Missing Index)演算子については触れませんでしたが、無視してよいという意味ではありません。必ず確認しましょう。ただし、盲目的にインデックスを作成してはいけません。同じ列に既存のインデックスがないかを確認し、データベース内で実行されている他のクエリへの影響も十分に考慮してください。

コメントやご質問がある場合は、フィードバックタブをご利用ください。私たちとの対話も歓迎します。