Query Go
SQL Server の実行計画
Query Go
チューニング

SQL Server の実行計画

SQL Server 実行計画の読み方。SSMS の実際のプラン、STATISTICS IO/TIME、Query Store。

SQL Server の実行計画 diagram

SHOWPLAN の種類と取り方

SQL Server の実行計画はXML ベースで、SSMS / Azure Data Studio でグラフィカルに表示できるのが大きな特徴です。取り方は大きく 2 系統:

  • 推定プラン（Estimated Execution Plan）: クエリを実行せずに計画のみ取得。SET SHOWPLAN_XML ON / SET SHOWPLAN_ALL ON、SSMS の Ctrl+L
  • 実際のプラン（Actual Execution Plan）: クエリを実行し、推定 + 実測行数・実測時間を含む計画を取得。SET STATISTICS XML ON、SSMS の Ctrl+M

推定だけ欲しければ SHOWPLAN_XML（XML）か SHOWPLAN_ALL（行テーブル形式）、実測まで欲しければ STATISTICS XML を使います。SSMS で開けば矢印アイコンのツリーとして可視化され、各ノードにマウスオーバーでプロパティが出ます。

sql
-- 推定プラン (XML)
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Orders WHERE UserId = 42;
GO
SET SHOWPLAN_XML OFF;

-- 実測込みのプラン
SET STATISTICS XML ON;
GO
SELECT * FROM Orders WHERE UserId = 42;
GO
SET STATISTICS XML OFF;

プランは「右から左」「上から下」に読む

SSMS のグラフィカルプランはデータフローの向きで矢印がつながっています。読み方は PostgreSQL/MySQL の木構造と逆で:

  1. 右端の葉ノード（テーブル / インデックスアクセス）が最初に実行される
  2. 結果が矢印に沿って左へ流れる
  3. 左端のルート（SELECT / INSERT 等）が最終出力

矢印の太さ = 流れる行数です。右下で細かった矢印が途中で急に太くなっている箇所は、結合で行数が爆発しているか、フィルタが遅いタイミングで適用されている可能性が高く、まず疑う場所です。

各演算子の相対コスト（%）も表示されます。「100% の演算子はどれか」を起点に、重いステップを特定します。ただしこれは推定コストベースなので、統計情報が古いと表示値も当てになりません。

よく見る演算子と Scan / Seek

SQL Server の葉ノードは大きく 4 種類:

  • Table Scan: ヒープ（クラスタ化されていないテーブル）の全件走査
  • Clustered Index Scan: クラスタ化インデックス（主キーが多い）の全件走査。実質フルテーブルスキャン
  • Index Scan: 非クラスタ化インデックスの全走査
  • Index Seek / Clustered Index Seek: インデックスの B-Tree を辿ってピンポイント取得。最も速い

「Scan を Seek に変える」がチューニングの基本。Scan になる原因は、インデックス不足のほかにSARGability 違反（WHERE 句の列に関数を適用、暗黙型変換）が定番です。

JOIN は Nested Loops / Hash Match / Merge Join の 3 種（他 RDBMS と同じ）。Key Lookup（非クラスタ化で絞り込んだ後にクラスタ化から残り列を取る）が大量に出ている場合は、カバリングインデックス（INCLUDE 付き）を追加すると劇的に減らせます。

sql
-- SARGability NG: 列に関数 → Index Seek が使えない
SELECT * FROM Orders WHERE YEAR(CreatedAt) = 2026;

-- OK: 範囲条件に書き換え → Index Seek 可
SELECT * FROM Orders
WHERE CreatedAt >= '2026-01-01'
  AND CreatedAt <  '2027-01-01';

-- Key Lookup を消すカバリングインデックス
CREATE INDEX IX_Orders_User_Created
  ON Orders (UserId, CreatedAt)
  INCLUDE (Total, Status);

STATISTICS IO / TIME で実 I/O と時間を測る

実行計画と併用したいのが SET STATISTICS IO ON / SET STATISTICS TIME ON。論理読み取り（キャッシュヒット）と物理読み取り（ディスクから）の区別、CPU 時間 / 経過時間がテーブルごとに出ます。

  • logical reads: バッファプール経由の読み取りページ数。最重要指標。チューニング前後で桁が減っていれば勝ち
  • physical reads / read-ahead reads: ディスクからの読み取り。初回は出るが 2 回目はゼロになるのが期待値
  • CPU time / elapsed time: CPU と壁時計。差が大きいなら待機（ロック / I/O）が支配的

「遅い原因がわからないときは、まず STATISTICS IO で logical reads が大きいテーブルを特定」が定石です。

sql
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT u.Name, COUNT(o.Id) AS Orders
FROM Users u
LEFT JOIN Orders o ON o.UserId = u.Id
GROUP BY u.Name;

-- 出力例
-- Table 'Orders'. Scan count 1, logical reads 412, physical reads 0, ...
-- Table 'Users'.  Scan count 1, logical reads 18,  physical reads 0, ...
-- SQL Server Execution Times: CPU time = 12 ms, elapsed time = 14 ms.

Estimated と Actual が乖離したとき

実際のプランでは各演算子にEstimated Number of Rows と Actual Number of Rows が並びます。この 2 つが桁違いに離れているのはほぼ常に問題のシグナルです:

  • 統計情報が古い → UPDATE STATISTICS / sp_updatestats で再収集
  • パラメータスニッフィング: 最初に実行した値で計画がキャッシュされ、別値で最悪になる。OPTION (RECOMPILE)、OPTIMIZE FOR UNKNOWN、OPTION (OPTIMIZE FOR (@p = ...)) で対処
  • 相関列: 複数列が相関している（例: 郵便番号と都市）と推定が外れる → フィルタ付き統計や複数列統計を作成

乖離を放置すると、オプティマイザが「Nested Loops で行ける」と思って組んだ結合が 100 万行を舐めに行って致命的に遅くなります。

Query Store — 計画履歴とレグレッション検知

SQL Server 2016 以降は Query Store がビルトイン。データベース単位で「どのクエリがいつ、どのプランで動き、どれだけ時間を使ったか」を記録する仕組みで、本番の性能調査の主役です。

  • 実行統計（CPU / duration / logical reads / 実行回数）をクエリ × プラン別に蓄積
  • プランがいつ変わったかを履歴で追える
  • 「前は速かったのに急に遅くなった」時に、以前のプランを強制（force plan）できる
  • SSMS の「Query Store」ノード配下にTop Resource Consuming Queries / Queries With Forced Plans 等のレポート UI あり

Azure SQL Database ではデフォルト有効。オンプレでも本番 DB ではまず有効化を推奨。過去は sys.dm_exec_query_stats や拡張イベントが主でしたが、Query Store の方が導入・運用とも圧倒的に楽です。

sql
-- 有効化
ALTER DATABASE MyDb SET QUERY_STORE = ON
  (OPERATION_MODE = READ_WRITE);

-- 合計 CPU Top 10 クエリ
SELECT TOP 10
    qt.query_sql_text,
    SUM(rs.count_executions)          AS execs,
    SUM(rs.avg_cpu_time * rs.count_executions) AS total_cpu
FROM sys.query_store_query_text qt
JOIN sys.query_store_query       q  ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan        p  ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
GROUP BY qt.query_sql_text
ORDER BY total_cpu DESC;

-- プラン強制 / 解除
EXEC sp_query_store_force_plan   @query_id = 42, @plan_id = 17;
EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 17;