この特集のトップページへ

9.6.2 クエリの実行計画の分析

　従来バージョンのSQL Serverでは，クエリを実行するとき，テーブルに定義されているインデックスを1つしか利用できなかった。しかし，SQL Server 7.0では，複数のインデックスを利用できるようになっている。

　クエリに指定されたWHERE句や結合句がテーブルに定義されたインデックスと一致する場合，SQL Server 7.0のリレーショナルエンジンは，それぞれのインデックスを評価して，コストが最低になるように，使用するインデックス，テーブルへのアクセス方法，テーブルの結合方法，クエリの処理ステップなどを決定する。その際，インデックスを利用するよりもテーブルスキャンのほうが低コストでクエリを実行できるなら，リレーショナルエンジンはテーブルスキャンを選択する（データ量が少ないテーブルに限り，インデックスを利用するよりもテーブルスキャンを利用したほうが低コストですむ）。

　リレーショナルエンジンが「遅いクエリ」に対して選択しているクエリの実行計画を確認するには，リレーショナルエンジン上でSHOWPLANオプション＊1を有効にする。

SHOWPLANオプション

　SHOWPLANオプションとは，クエリの実行時にSQL Serverがどのような処理を実行しているかを表示するためのオプションである。このオプションを有効にしてクエリを実行すると，リレーショナルエンジンは，個々のデータ操作言語（DML），Transact-SQLステートメント，ストアドプロシージャ，バッチ内から呼び出されるトリガやストアドプロシージャなどの各ステップで，次のような情報を出力するようになる。

  • 実行されたテーブルスキャン
  • 実行されたクラスタ化インデックススキャン
  • 実行された結合や集計などの演算操作
  • 操作によって出力される行数
  • 操作によって出力される見積もり行サイズ
  • 操作に必要なI/Oコスト
  • 操作に必要なCPUコスト
  • クエリの実行中に操作される回数

　本書では便宜上，これらの情報を「SHOWPLAN情報」と呼ぶことにする。

　たとえば，SHOWPLANオプションを有効にしてSELECTステートメントを実行すれば，SQL Serverがデータを取得するのにテーブルスキャンを使用しているのか，それともインデックスを使用しているのかを確認できる。

　SHOWPLANオプションを有効にする方法は，次の3つである。

○SET SHOWPLAN_TEXT ONステートメントを実行する方法
　このステートメントを実行したあとでクエリを実行すると，クエリの実行計画の情報が表示されるようになる。実際にTransact-SQLステートメントを実行することはなく，ステートメントの実行方法にかかわる情報（ステートメント，論理演算子，または物理演算子）のみを表示する。構文は次のとおりである。

　　　SET SHOWPLAN_TEXT {ON | OFF}

Fig.9-28 SET SHOWPLAN_TEXT ONの実行例（クリックすると拡大）


【出力列の説明】
StmtText……この列には，Transact-SQLステートメントのテキストまたは演算子（物理演算子または論理演算子）の説明が格納される。

○SET SHOWPLAN_ALL ONステートメントを実行する方法
　このステートメントを実行したあとでクエリを実行すると，クエリの実行計画に関するすべての情報が表示されるようになる。実際にTransact-SQLステートメントが実行されるわけではなく，ステートメントの実行方法と，実行に必要となるリソースの見積もりにかかわる詳細情報が表示される。

　　　SET SHOWPLAN_ALL {ON | OFF}

Fig.9-29 SET SHOWPLAN_ALL ONの実行例（クリックすると拡大）


【出力列の説明】
StmtText……この列には，Transact-SQLステートメントのテキストまたは演算子（物理演算子または論理演算子）の説明が格納される。
StmtId……現在のバッチに含まれるステートメントの数を示す。
NodeId……クエリ内のノードのIDを示す。
Parent……親ステップのノードのIDを示す。
PhysicalOp……このノードが実行する物理演算子を示す。
LogicalOp……このノードが実行する論理演算子（関係代数演算子）を示す。
Argument……実行される演算にかかわる補足情報が格納される。
DefinedValues……この演算子によって定義される値のリストがカンマで区切られて示される。これらの値は，クエリ（たとえば，SELECTステートメントやWHERE句）に指定された計算式であるか，またはこのクエリを処理するためにリレーショナルエンジンが定義した内部値のいずれかである。
EstimateRows……この演算子によって出力される見積もり行数を示す。
EstimateIO……この演算子で消費されるディスクの見積もりI/Oコストを示す。
EstimateCPU……この演算子で消費される見積もりCPUコストを示す。
AvgRowSize……この演算子から出力される行の見積もり平均サイズ（バイト数）を示す。
TotalSubtreeCost……この演算と，この演算のすべての子演算の見積もり累積コストを示す。
OutputList……出力される列がカンマで区切られて一覧表示される。
Warnings……演算に対する警告メッセージが表示される。警告メッセージに“NO STATS:()”という文字列が含まれる場合は，使用できる統計情報がなかったことを示している。また，“MISSING JOIN PREDICATE”という文字列が含まれる場合には，結合述語のない結合（テーブルを含む）が実行されていることを示す。この場合，予想よりも実行時間がかかり，大きな結果セットを返すクエリが作成される可能性がある。
Type……ノードのタイプを示す。各クエリの親ノードであれば，Transact-SQL ステートメント（たとえば，SELECT，INSERT，EXECUTEなど）が表示される。
Parallel……並列実行が行われるか否かを示す。0のときは演算を並列実行せず，1のときは並列実行する。
EstimateExecutions……クエリを実行中にこの演算子が実行される見積もり回数を示す。

○クエリアナライザにおける［実行プランの表示］オプションを選択する方法
　クエリアナライザの［実行プランの表示］オプションは，リレーショナルエンジンが選択したデータ取得方法をグラフィカルに表示し，ステートメントとクエリの実行をアイコンによって示すようになっている。実行プランをグラフィカルに表示させたい場合には，クエリアナライザのメニューから［クエリ］−［実行プランの表示］＊2または［推定実行プランの表示］＊3をチェックする。これらのオプションを有効にしている状態でクエリを実行すると，［実行プラン］パネルが表示される。このパネルを開くと，SHOWPLAN情報を確認することができる。

　クエリの実行はいくつかのステップを経て最終的な結果が得られる。たとえば，単純なSQLステートメントである“SELECT * FROM authors”を実行する場合でも，テーブルの読み込みステップと出力ステップを経て初めて結果が得られる。これらのステップをノードと呼び，最初のステップを親ノード，そして次のステップを子ノードと呼ぶ。これらのノードは，親ノードを頂点にして右から左にツリー構造で表示される（Fig.9-30）。そして，各ノードは前のノードからの出力を入力として結果を作成し，次のノードに出力するために入力データに対して実行するであろうデータ操作を論理演算子または物理演算子で示している＊4。

　グラフィカルモードで出力されたSHOWPLAN情報には，リレーショナルエンジンが選択した最低コストのクエリ実行計画が表示されるので，各ノード（実行ステップ）で最もコストを必要としているノードを探す。そして，ノードで見積もられているI/Oコスト，CPUコスト，行長，行数などを検証し，そのコストを最小にするような方法を検討する。そのクエリの実行時間に占める比率の高いデータ操作のコストを重点的に下げるほうが，比率の低いデータ操作のコストを下げるよりも効率的である。

Fig.9-30 クエリアナライザによるグラフィカルなSHOWPLAN情報の表示（クリックで拡大）


　各ノードにおける情報を検証したい場合には，クエリアナライザ上で表示されたノードにマウスカーソルを合わせ，詳細な情報（物理操作，論理操作，行数，見積もり行サイズ，I/Oコスト，CPUコスト，実行数，コスト，サブツリーコストなど）を閲覧する。ただし，表示される数値は見積もり値であり，実際に実行に要したCPUコストやI/Oコストではないので注意してほしい。表示される情報の意味は，SET SHOWPLAN_ALL ONステートメントを実行した場合と同じである。

【表示の情報の意味】
物理操作……テーブルからデータを物理的に取得するのに使用する方法を表す。［Clustered Index Scan］，［Index Seek］，［Bookmark Lookup］，［Hash Join］，［Nested Loop］などがある。列の統計がない場合や結合述部がない場合は，アイコンを赤色で表示して警告する。この場合は，必ずしも最適な実行計画が選択されないことがある。
論理操作……テーブルからデータを論理的に取得するのに使用する方法を示す（物理操作と一致する論理操作）。
見積行数……そのノードで出力されるデータ行数を示す。
見積行サイズ……そのノードで出力される見積もり平均行長を示す。
I/Oの見積コスト……必要な見積もりI/O時間を示す。
CPUの見積コスト……必要な見積もりCPU時間を示す。
見積実行数……操作が実行された回数を示す。
見積コスト……この操作を実行しているリレーショナルエンジンに対するコストと，全体に対する比率を示す。
見積サブツリーコスト……この操作および同じサブツリーで処理しているすべての操作を実行しているリレーショナルエンジンに対する総コストを示す。

COLUMN
　SQL Server 6.5のリレーショナルエンジンで実行計画を検証するには，次のように操作する。
1.ISQL/wの［クエリオプション］−［クエリプランを表示］フラグを設定し，選択された実行計画を確認する。
2.SET SHOWPLAN ONステートメントを実行し，選択された実行計画を確認する。
3.次のようにトレースフラグ302を指定し，オプティマイザの決定したインデックスを検証する。
　　DBCC TRACEON(302)
4.次のようにトレースフラグ310を指定し，クエリプランの選択と結合順を検証する。
　　DBCC TRACEON(310)
5.オプティマイザが選択した結合順を変更したい場合には，次のようにFORCEPLANオプションを有効にする。
　　SET FORCEPLAN ON
  1. クライアント側のJet 3.5/4.0でSHOWPLANオプションを有効にしてクエリの実行計画を調べることができる。その場合はレジストリを次のように設定する。すなわち，HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\3.5\Engines\DebugまたはHKEY_ LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\Engines\Debugの下にJETSHOWPLAN="ON"を追加する。実行計画はshowplan.outファイルに出力される。
  2. ［実行プランの表示］を有効にすると，クエリの実行結果と実行計画が表示されるようになる。この設定を有効にすると，明示的に無効とするか，接続が切断されるまで有効となる。
  3. 入力されているクエリに対する実行計画のみが表示される。クエリが実行されることはない。
  4. テーブルスキャン，結合，集計など，リレーショナルエンジンによって実行されるデータ操作（演算）がツリーのノードとして表示される。
前へ Chapter 9　13/46 次へ