SQL Serverで、実行計画を学ぶ！第一回:インデックス効果を確認する方法

はじめに

前に、データベース（SQL Server）で本格的な実行計画を分析する業務を担当した際に、
データベースのパフォーマンス改善の奥深さを学ばせてもらう機会がありました。

基本的に、エンタープライズ級のストアドやバッチでは、速度改善をするためにクエリを大幅に変更することはほとんどありません。理由は、ビジネス要件への影響範囲が広範囲に及ぶからです。

ですので基本的には、インデックスによる効果検証を何度も試すというパターンが多いものと感じています。（もちろんクエリのリファクタリングをするところだって、全然あります）。

いずれにしても、データベースのパフォーマンス改善において、インデックスの効果を数値で実感することは重要です。

しかし、「インデックスを作ったら速くなった」という体感だけでは、どの程度の改善があったのか、なぜ改善したのかが分かりにくいものです。

そこで今回は、SQL Serverの実行計画を使って実行計画を再入門しようと思います。

第一回目の今記事では、インデックスの効果を具体的な数値で可視化する方法を紹介します。私が体験したケースを基に、10分かかっていたクエリ実行が5分に短縮された改善事例を詳しく分析していきます。

実行計画の読み方基礎

実行計画とは何か

SQL Serverの実行計画は、クエリオプティマイザーがSQL文をどのように実行するかを決定した処理手順を表示したものです。 ツリー構造で表現され、各ノードが特定の処理（テーブルスキャン、結合、ソートなど）を表します。

重要な指標の見方

実行計画で注目すべき主要な指標はこちらです。

・EstimateRows: 推定される処理行数

・EstimateIO: I/O（入出力）にかかる推定コスト

・EstimateCPU: CPU処理にかかる推定コスト

・TotalSubtreeCost: そのノード以下の総コスト

・PhysicalOp/LogicalOp: 実際の処理方法

実行計画の読み方のコツ

実行計画を効率的に読み解くための基本的なアプローチを、具体例と共に紹介します。

その１. 右から左へ読む: データの流れを追う

例：顧客情報を検索するクエリの実行計画
SELECT * FROM customers WHERE city = 'Tokyo'

[Hash Match] ← [Table Scan: customers]
     ↑              ↑
   結果出力      テーブル読み込み

実行計画はデータの流れを右から左に表現します。

1.右側：customers テーブルをスキャン

2.左側：条件 city = 'Tokyo' でフィルタリング

3.結果を返す

その２. 太い矢印に注目: データ量の流れを把握

例：効率的なクエリ vs 非効率なクエリ

【効率的】
[Output] ←細い矢印← [Index Seek] ←細い矢印← [Index: ix_city]
 100行              100行              100行

【非効率】  
[Output] ←細い矢印← [Filter] ←太い矢印← [Table Scan]
 100行              100行          1,000,000行

矢印の太さは処理される行数に比例します。

ポイントとして、太い矢印から細い矢印への変化が、効率的な絞り込みが行われ、太い矢印が続くと大量データの全体スキャン（無駄な処理）が続いていると考えられます。

その３. 高コストノードを特定: ボトルネックを見つける

例：複雑な結合クエリの分析

全体コスト: 45.67
├─ Sort (15%) : 6.85          ← 軽微
├─ Hash Join (20%) : 9.13     ← 中程度  
├─ Index Seek (5%) : 2.28     ← 軽微
└─ Table Scan (60%) : 27.41   ← ★ボトルネック★

全体コストに占める割合でボトルネックを特定します。
上記の例ではわかりやすいですが、大体以下のように基準を持っていると判断が楽になります。

１：60%以上：最優先で改善対象

２：20-60%：次の改善候補

３：20%以下：様子見

その４. 処理コスト:数値の見方のコツ

例：一般的な処理コストの目安

Index Seek    : 0.01 - 1.0    (高速)
Index Scan    : 0.1 - 10.0    (中程度)
Table Scan    : 1.0 - 100.0+  (低速)
Hash Join     : 0.5 - 50.0    (データ量依存)
Sort          : 0.1 - 20.0    (データ量・複雑さ依存)

各コストごとに、数値が出ていますが私が行ってきたのは、大体の確立でTable Scanがあったらそこを徹底的に修正するやり方を取っていました（笑）。

このやり方でだいぶ乱暴なので、以下のようにちゃんと判断基準を念頭に置いておくとよいでしょう。

・コスト < 1.0：通常問題なし

・コスト 1.0-10.0：注意深く監視

・コスト > 10.0：改善を検討

・コスト > 50.0：優先的に改善

実際のケース: インデックス適用前後の比較

以下のようなMERGE文で、KOSEKI_RYOKINテーブルに対する条件絞り込みがボトルネックになっていました。

SELECT ...
FROM KOSEKI_RYOKIN
WHERE (
    ISNULL(ENABLE_START_DATE, CAST('1111111' AS DATETIME)) <= GETDATE()
)
AND (
    GETDATE() <= ISNULL(ENABLE_END_DATE, CAST('99999999' AS DATETIME))
)

インデックスを施したところ、改善前（処理時間10分)⇒改善後(処理時間5分)に大幅に改善することができました。

この改善には、index scanの存在が大きなカギとなりました。

Clustered Index Scan vs Index Seek の違い

以下は改善前の実行計画です。

PhysicalOp: Clustered Index Scan
LogicalOp: Clustered Index Scan
Object: KOSEKI_RYOKIN.PK_S_KOSEKI_RYOKIN
EstimateRows: 169,122.7
EstimateIO: 1.925347
EstimateCPU: 0.0956201
TotalSubtreeCost: 2.020967

改善前に出ていたClustered Index Scanは以下のような特徴があります。

・テーブル全体（または大部分）をスキャン

・条件に関係なく全データページを読み込み

・I/Oコストが高い

・WHERE条件の評価を行いながらスキャン

インデックス適用後: Index Seek

さて、indexを付与してみます。日付を指定したインデックスを付与する以下のクエリを実施したところ、それぞれ数値が変化しました。

CREATE NONCLUSTERED INDEX IDX_KOSEKI_RYOKIN_START_END 
ON KOSEKI_RYOKIN (ENABLE_START_DATE, ENABLE_END_DATE) 
INCLUDE(UP_DOWN_PRICE);

PhysicalOp: Index Seek
LogicalOp: Index Seek
Object: CN_ZS_KOSEKI_RYOKIN.IDX_CN_KOSEKI_RYOKIN_START_END
EstimateRows: 169,122.7
EstimateIO: 0.288802
EstimateCPU: 0.0669341
TotalSubtreeCost: 0.355736

改善後には、Index Seekが使われるようになりました。
このSeekは、簡単に言えばインデックスを使って必要な範囲のみアクセスができているということです。

📊 指標改善結果

では、実際どれくらい改善されたのか表にしてみてみましょう。

指標名	改善前	改善後	削減量	削減率
EstimateIO	1.925347	0.288802	1.636545	85.0%
EstimateCPU	0.09562010	0.06693410	0.02868600	30.0%
TotalSubtreeCost	2.020967	0.355736	1.665231	82.4%

いやー、だいぶ改善されてますね！
インデックスを付与しただけで、なぜこれほどまでに改善できているのでしょうか。

理由は、大きく分けて3つ挙げられます。

1. I/O削減
今回のインデックスが付与されたことにより、条件に合致するデータページのみアクセスできるようになりました。
要する、不要な読み込みが減ったのですね。

それによって、ディスクI/O回数の大幅削減されています。

2. CPU削減の理由
1.の理由に近いですが、読み込みが減ったことにより、
効率的な条件評価が効率的になったことを意味します。

それに伴い、CPUももちろん削減されていることが要因として挙げられます。

3. 物理操作の変化
ScanとSeekの違いですね。
Scanは、順次的にデータを読み込みます。要するに、すべてのテーブルを一度読み込んで、そこから条件一致のデータを探しているというやり方です。

一方ではSeekインデックスを使って直接目的のデータにアクセスしています。

直接目的のデータにアクセスすることにより改善されたということは、今回のデータではI/Oの改善が大きなコスト削減につながったと考えられます。

総コスト削減: 1.665231
├─ I/Oコスト削減: 1.636545 (98.3%)
└─ CPUコスト削減: 0.028686 (1.7%)

まとめ

SQL Serverの実行計画を活用することで、インデックスの効果を具体的な数値で可視化できます。今回のケースでは：

・I/Oコスト85%削減による大幅な性能向上
・Clustered Index Scan → Index Seek への処理方法変更
・実行時間50%短縮（10分→5分）の実現

実行計画の読み方を習得し、適切なインデックス設計を行うことで、データベースパフォーマンスを大幅に改善できます。数値に基づいた改善により、説得力のある最適化提案も可能になります。

しかし、なぜ実行計画のコスト削減は小さいのに実行時間は大幅に短縮されるのか。
次記事では、その謎について調査して書きたいと思います。