SQL Server | 実行プランの確認方法

【注目記事】

・SEの転職体験談 | 受託開発から自社製品開発へ
・SIer勤務のSEがパソナキャリアに相談してみた
・SIer勤務のSEがマイナビエージェントに相談してみた

パフォーマンスチューニングをする上で、SQLの実行計画を確認する事はとても重要です。

大半のDMBSは実行計画を確認する機能を提供していますが、SQL Serverでは、「実行プランの確認」という機能で提供しています。

本記事では、この機能の使用方法をご紹介します。

DBMSの実行計画書

DBMSの実行計画書とは

DBMSの実行計画書は、受け取ったSQLをDBMSがどのような手順で処理するかを決める計画書です。

実行計画書には、データ抽出の方法や結合の順番などが含まれます。

DMBSが実行計画書を生成する流れ

DMBSはSQLを受け取ると、オプティマイザにSQLを渡します。

オプティマイザは、カタログマネージャが管理する統計情報※をもとに複数のプランを生成し、それぞれのプランに対してコストを評価し、コストが低いプランを選びます。

最後に、オプティマイザが生成した複数の実行計画書を、プラン評価で1つに絞ります。

DBMSは限られた時間の中で最適な計画書を作成しようとしますが、時間が限られていたり、判断が難しかったりするので必ずしも最適なものができるわけではありません。

そのため実行プランを確認し、問題があれば計画書を修正することで、パフォーマンスを改善することができます。

※統計情報の詳細は「統計情報とは」を参考にしてください。

(参考 『SQL実践入門』| ミック)

SQL Serverの実行プランとは

実行プランとは、SQL Severが作成した実行計画書を確認するための機能です。

実行プランの確認方法

確認手順

実行プランは、SQL Server Management Studioを利用することで簡単に確認できます。

1. ツール バーの 「実際の実行プランを含める」をクリックする
2. 実行プランを確認したいクエリを実行する
3. 「実行プラン」タブを押下して、表示された実行プランを確認する

実行プランの見方

 クエリの流れ

実行プランは実行された順に、右から左に記載されます。

バッチ相対

バッチ相対は、実行プランの上部に記載されます。

1つのトランザクションの中に、複数のステートメント(クエリ)が存在する場合、全体を100%として、それぞれどれだけの比率でコスト(内部測定時間)を要したのかが表示されます。

Management Studio で実行プランを確認した際のコストについて

プロパティ情報

各処理にフォーカスをあてると、プロパティ情報が表示されます。

重要な項目は下記の通りです。

• I/Oの推定コスト : ディスクからデータを読み取るに必要な推定コスト※です。
• CPUの推定コスト : CPUの動作に必要な推定コスト※です。
• 操作の推定コスト : I/OとCPUを合算した推定コスト※です。
• サブツリーの推定コスト : 対象の処理を含む、累計の推計コスト※です。

※推定コストの説明は、「SQL Server | 推定コストとは」をご覧ください。

実行プランの中身を理解するために必要な知識

実行プランの中身を理解するためには、SQL Serverがクエリをどのように実行するのかを理解する必要があります。

そのための基本的な知識をご紹介します。

並列実行(Parallelism)と直列実行

クエリを処理する際に、同時に複数の処理をする並列実行がSQL Serverでは可能です。

並列実行をすれば、一度に1つの処理しかできない直列実行よりも処理時間が短く済みます。

並列実行をするにはいくつかの条件を満たす必要があります。

実行プランを確認し直列実行になっている場合は、クエリを並列実行の条件を満たすように修正することで、パフォーマンスの改善が望めます。

(参考) 並列処理の次数

クラスター化インデックス と 非クラスター化インデックスの違い

クラスター化インデックス

クラスター化インデックスは、テーブルのデータをインデックスに指定した列の値で並び替えて格納します。

そのため、ソートや範囲検索を高速で処理できます。

ただし、クラスター化インデックスは、1つのテーブルに1つしか指定できません。

非クラスター化インデックス

非クラスター化インデックスは、データの並び替えはせずに、インデックスファイルにデータへのポイントを格納します。

データはソートされずに格納されるため、クラスター化インデックスに比べてソートや範囲検索は遅くなりますが、1つのテーブルに複数指定できます。

クラスター化インデックス と 非クラスター化インデックスの違い (SQL Server Tips)

インデックスの構造と内部動作１

index scanとindex seek

index seekはBツリーインデックスをルートから順番に辿って、最短の手順でデータを探します。

(参考) 検索ツリー (Bツリー) がインデックスを高速に動作させる

index scanはインデックスのリーフノードを順に調べます。

インデックスを使用しますが、場合によってはリーフノードをすべて検索することになるので、index seekより処理に時間がかかります。

(参考) キャッシュを無駄遣いしないようにクエリを書く

なおテーブルの行の大部分が検索対象になる場合は、index scan(またはtable scan)の方がインデックスを利用する手間が省ける分効率が良くなります。

table scanと index scan

scanには複数の種類があります。

table scanはテーブルのデータを全て確認します。辞書を1ページ目から順に読んで探すイメージです。

一方index scanは、あらかじめ作成した索引(インデックス)を使用してデータを確認します。

索引は列の値と、その値を持つ行がテーブルの何行目にあるのかの2つの情報を持ちます。

また、値を素早く見つけられるように、索引は値の順番に並べられます。

データを確認する際は、索引をみて何行目にデータがあるのかを見つけてから実際のテーブルを検索します。

(参考) SQLチューニングの基礎知識　「データアクセスと索引（インデックス）」の関係を理解する 

Merge Joinの種類

Right Anti Semi Join : 左側のテーブルに、右側のテーブルの結合するキーが存在しない行を取得する

Right Semi Join : 左側のテーブルに、右側のテーブルの結合するキーが存在する行を取得する

その他の必要な知識

(参考) プラン表示の論理操作と物理操作のリファレンス

(参考) インデックスの構造と内部動作

まとめ

実行プランを確認することは、SQL Serverでパフォーマンスチューニングするために避けては通れません。

積極的に利用して、使いこなせるようになりましょう。

参考書籍

SQLスキルを高めるためのおススメ書籍