sql-server-performance

SQL Server のパフォーマンス

使用する場面

• 以前は高速だったクエリが遅くなった
• 実行プランを読み取り、ボトルネックを見つける
• インデックス戦略（クラスター化、カバリング、フィルター付き）を選択する
• ロック、ブロッキング、デッドロックを診断する
• トランザクションログを肥大化させない一括 INSERT/UPDATE/DELETE を記述する
• ストアドプロシージャのパラメーター スニッフィングを修正する
• 一時テーブルとテーブル変数のどちらを使うか判断する
• 待機統計を分析し、リソースのボトルネックを特定する

使用しない場面： アプリケーションのスキーマ設計（テーブル設計、命名規則、アクセス制御）、バックアップ／復元戦略、高可用性の構成、レプリケーションのセットアップ。

診断フローチャート

クエリが遅い場合は、次の手順に従ってください。各手順で原因を絞り込んでから、次に進みます。

ステップ 1 — 実際の実行プランを取得する

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- SSMS で Ctrl+M（実際の実行プランを含める）を押してからクエリを実行します

次の点を確認します。

• キー ルックアップ — 非クラスター化インデックスのシークに続き、行ごとにクラスター化インデックスを検索しています。1,000 行以上では、これが大きな負荷になります。対処法：不足している SELECT 列を NCI の INCLUDE リストに追加します。
• 演算子間の太い矢印（太いコネクタ） — 推定行数 ≪ 実際の行数。カーディナリティ推定に失敗しています。対処法：統計を更新し、パラメーター スニッフィングを確認します。
• Hash Match 上の黄色い警告三角形 — メモリ許可が小さすぎたため、ハッシュが tempdb にスピルしました。対処法：メモリ許可が適切なサイズになるよう、統計を更新します。
• 大きなテーブルのテーブル スキャン — クラスター化インデックスがないか、SARG 不可能な述語によってスキャンが強制されています。対処法：クラスター化インデックスを追加するか、述語を書き換えます。
• インデックス スプール — SQL Server が一時インデックスをその場で作成しました。対処法：永続インデックスを作成します。

ステップ 2 — 待機統計を確認する

-- ステップ 1：問題が発生する時間帯の前にスナップショットを取得します
SELECT wait_type, wait_time_ms INTO #w FROM sys.dm_os_wait_stats;
-- ...60 秒待つか、クエリが遅い時間帯が終わるまで待ちます...
-- ステップ 2：差分から、その時間帯に待機時間を消費した要因が分かります
SELECT c.wait_type, c.wait_time_ms - b.wait_time_ms AS delta_ms
FROM sys.dm_os_wait_stats c JOIN #w b ON b.wait_type = c.wait_type
WHERE c.wait_time_ms > b.wait_time_ms
  AND c.wait_type NOT IN ('SLEEP_TASK','LAZYWRITER_SLEEP','WAITFOR','CXCONSUMER')
ORDER BY delta_ms DESC;
DROP TABLE #w;

主な待機	考えられる原因
LCK_M_*	ブロッキング／ロック。locking-blocking.md を参照
PAGEIOLATCH_SH	インデックス不足、またはバッファープールがコールド状態
CXPACKET	並列処理の不均衡、または MAXDOP が高すぎる
RESOURCE_SEMAPHORE	メモリ許可の不足。ハッシュ／ソートのスピル
WRITELOG	トランザクションログの I/O がボトルネック
SOS_SCHEDULER_YIELD	CPU の飽和

詳しい手法と DMV クエリについては、wait-stats.md を参照してください。

ステップ 3 — インデックスを評価する

-- 不足しているインデックスの推奨事項の上位（再起動時にリセットされるため、速やかに対応してください）
SELECT TOP 10
    d.statement                 AS [Table],
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    ROUND(s.avg_total_user_cost * s.avg_user_impact
          * (s.user_seeks + s.user_scans), 0) AS estimated_improvement
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats s
    ON g.index_group_handle = s.group_handle
JOIN sys.dm_db_missing_index_details d
    ON g.index_handle = d.index_handle
WHERE d.database_id = DB_ID()
ORDER BY estimated_improvement DESC;

クラスター化キーの選択、カバリングインデックス、インデックス過剰作成のトレードオフについては、index-strategy.md を参照してください。

ステップ 4 — 統計の鮮度を確認する

-- ヒストグラムの最大値と実際の最大値を比較します（昇順キーの問題）
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_OrderDate') WITH HISTOGRAM;
SELECT MAX(OrderDate) AS actual_max FROM dbo.Orders;
-- actual_max > last RANGE_HI_KEY の場合、最近の行に対する統計は古くなっています
-- Check modification counter
SELECT s.name, sp.last_updated, sp.modification_counter,
       CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(5,2))
           AS pct_modified
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.Orders');

ヒストグラムの解釈と更新戦略については、statistics-tuning.md を参照してください。

ステップ 5 — パラメータースニッフィングを診断する

-- Multiple plans for the same proc = sniffing instability
SELECT qsq.query_id, COUNT(DISTINCT qsp.plan_id) AS plan_count,
       qsqt.query_sql_text
FROM sys.query_store_query       qsq
JOIN sys.query_store_plan        qsp  ON qsp.query_id      = qsq.query_id
JOIN sys.query_store_query_text  qsqt ON qsqt.query_text_id = qsq.query_text_id
GROUP BY qsq.query_id, qsqt.query_sql_text
HAVING COUNT(DISTINCT qsp.plan_id) > 3
ORDER BY plan_count DESC;

以下の「パラメータースニッフィング」セクションと、execution-plans.md を参照してください。

インデックス戦略

テーブルには、適切に選択されたクラスター化インデックスを 1 つと、セカンダリアクセスパターンに合わせたカバリング非クラスター化インデックスを用意します。すべてのインデックスには書き込み時のコストが伴うため、OLTP テーブルの非クラスター化インデックスは合計 3～5 個に抑えてください。

クラスター化インデックスの選択 — 次の条件を満たすキーを選びます。

• 狭い — クラスター化キーは、すべての非クラスター化インデックスのリーフ行にコピーされます。INT（4 バイト）と UNIQUEIDENTIFIER（16 バイト）の違いは、すべてのインデックスと数百万行にわたって増大します。
• 単調増加する — ランダムな挿入は 50/50 ページ分割と深刻な断片化を引き起こします。INT IDENTITY、BIGINT IDENTITY、または NEWSEQUENTIALID() を使用します。
• 一意である — SQL Server は、重複する値に 4 バイトの一意性識別子を暗黙的に追加し、インデックスを肥大化させます。
• 不変である — クラスター化キーを更新すると行が物理的に移動し、すべての非クラスター化インデックスにも更新が波及します。

非クラスター化インデックスの列の順序：

1. 等価述語を先に指定します（col = @val）
2. 範囲述語を次に指定します（col BETWEEN、col > @val）
3. ORDER BY 列を最後に指定します（インデックス内で等価列の後に続く場合、並べ替えは不要です）

カバリングインデックスは Key Lookup をなくします。SELECT のみに使用する列を INCLUDE に追加します。

-- Before: every row causes a Key Lookup back to the clustered index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
    ON dbo.Orders (CustomerID);

-- After: covering index — no Key Lookup
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Covering
    ON dbo.Orders (CustomerID)
    INCLUDE (OrderDate, Status, TotalAmt);

フィルター付きインデックスは行のサブセットを対象とするため、メンテナンスコストが低く、選択性が高くなります。

-- Index only active orders — much smaller than a full-table NCI
CREATE NONCLUSTERED INDEX IX_Orders_Active
    ON dbo.Orders (CustomerID, OrderDate)
    INCLUDE (Status)
    WHERE Status = 1;

-- Unique constraint on nullable external key (NULLs excluded)
CREATE UNIQUE NONCLUSTERED INDEX UX_Orders_ExternalRef
    ON dbo.Orders (ExternalRef)
    WHERE ExternalRef IS NOT NULL;

断片化のしきい値、フィルファクターのガイダンス、および不足インデックス DMV の使用方法については、index-strategy.md を参照してください。

SARGability

述語が SARGable であるとは、オプティマイザーがすべての行をスキャンする代わりに、インデックスシークを使用できることを意味します。ルールは、フィルター対象の列を関数や式で囲まないことです。

SARGable ではない（スキャンが発生）	SARGable な代替方法
WHERE YEAR(OrderDate) = 2025	WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
WHERE ISNULL(col, '') = @Val	列が NOT NULL であることを確認し、WHERE col = @Val を使用します
WHERE col LIKE '%search'	末尾にワイルドカードを付けて使用します：WHERE col LIKE 'search%'
WHERE DATEDIFF(day, col, GETDATE()) < 30	WHERE col > DATEADD(day, -30, GETDATE())
WHERE CAST(col AS VARCHAR) = '42'	パラメーターの型を列の型に合わせます
WHERE LEFT(LastName, 3) = 'Smi'	WHERE LastName LIKE 'Smi%'
WHERE col + 0 = @Val	WHERE col = @Val（列に算術演算を適用しないでください）

暗黙的な型変換も関数と同じ効果があり、オプティマイザーはすべての行を変換する必要があります。パラメーターの型を列の型と正確に一致させてください。実行プランの XML で PlanAffectingConvert 警告を確認してください。

パラメータースニッフィング

SQL Server は、ストアドプロシージャの初回実行時に渡された実際のパラメーター値を使って、実行プランをコンパイルします。そのプランはキャッシュされ、以降、異なるパラメーターを指定して実行した場合も再利用されます。これは 90% の場合に有益ですが、データ分布に偏りがあり、スニッフィングされた値が典型的でない場合には問題になります。

推奨順の戦略（この順序で使用してください）：

1. OPTIMIZE FOR UNKNOWN — 平均的な分布統計を使って安定したプランを生成します。多数の異なるパラメーター値が考えられ、特定の値が突出していない場合に推奨されます：

CREATE PROCEDURE dbo.GetOrders_ut
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, TotalAmt
    FROM   dbo.Orders
    WHERE  CustomerID = @CustomerID
    OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));
END;

2. OPTION (RECOMPILE) — 実行ごとに新しいプランを生成し、実際のパラメーター値を埋め込みます。オプティマイザーは毎回最適なプランを見つけますが、呼び出しごとに CPU コンパイルコストがかかります。実行頻度は低いものの、パラメーター値の変動が大きいプロシージャ（夜間レポート、管理ツールなど）に使用してください：

SELECT OrderID, OrderDate, TotalAmt
FROM   dbo.Orders
WHERE  CustomerID = @CustomerID
OPTION (RECOMPILE);   -- プロシージャの WITH RECOMPILE ではなく、ステートメントレベルの指定

3. ローカル変数へのコピー — 避けてください。 @CustomerID をローカル変数に代入するとスニッフィングを無効にできますが、一般的なケースでスニッフィングがもたらす利点も失われます。OPTIMIZE FOR UNKNOWN なら、同じ統計的な動作を、意図を明示して文書化した形で実現できます。

-- BAD: スニッフィングが有効な場合も含め、完全に無効にします
DECLARE @CID INT = @CustomerID;
SELECT ... WHERE CustomerID = @CID;

Query Store が有効な SQL Server 2022（互換性レベル 160）では、Parameter-Sensitive Plan Optimization（PSPO）によって、偏った分布に対する複数のプランバリアントを自動的に保持できます。これは、SQL Server 2019 以前で上記の戦略に代わるものではありません。

一時テーブルとテーブル変数の比較

ディメンション	一時テーブル（#t）	テーブル変数（@t）	CTE
統計情報	あり — 自動作成	なし（2019 より前は 1 行、2019 以降は遅延コンパイル）	なし
インデックスのサポート	すべての種類	PRIMARY KEY、UNIQUE のみ	なし
並列処理	あり	あり	あり
再コンパイルのトリガー	スキーマ／統計情報の変更時	まれ	なし
ROLLBACK 後も存続	いいえ — 削除されます	はい — 内容は保持されます	いいえ
スコープ	セッションおよび呼び出されたプロシージャ	現在のバッチのみ	現在のステートメント
TempDB キャッシュ	あり — プロシージャ内	同様	なし

選択の基準：

• 行数が数百を超える場合、中間結果にインデックスが必要な場合、または後続の結合で正確なカーディナリティ推定が必要な場合は、一時テーブルを使用します。
• セットが小さく上限が明確な場合（1～100 行）、再コンパイルのトリガーを避けたい場合、または ROLLBACK 後もデータを保持する必要がある場合（例：処理開始前に挿入する監査ログ）は、テーブル変数を使用します。
• 結果を一度だけ参照する場合は、CTEを使用します。CTE はマテリアライズされず、参照のたびにサブクエリが再実行されます。CTE を 2 回参照すると、ソースが 2 回スキャンされます。

SQL Server 2019 のテーブル変数の遅延コンパイル（互換性レベル 150 以上）：オプティマイザーは、テーブル変数を使用するステートメントのコンパイルを、テーブル変数にデータが格納された後まで遅らせ、実際の行数を使用します。これにより、2019 以降の環境では「1 行」という推定値の問題がほぼ解消されます。列レベルの統計情報とキー以外の列のインデックスでは、引き続き一時テーブルが優位です。

-- 遅延コンパイルが有効か確認する（互換性 150 以上）
SELECT name, value FROM sys.database_scoped_configurations
WHERE name = 'DEFERRED_COMPILATION_TV';

よくある間違い

間違い	修正方法
SARG 不可能な WHERE 句	絞り込み対象の列を関数で囲まないでください — 関数はパラメーターに適用します
行数の多いクエリでの Key Lookup	必要な SELECT 列を NCI の INCLUDE リストに追加します
「読み取り性能向上」のために NOLOCK を使用する	代わりに RCSI を有効にします — 一貫性のある読み取りができ、共有ロックのオーバーヘッドもありません
ローカル変数へのコピーでパラメーター スニッフィングを修正する	OPTIMIZE FOR UNKNOWN またはステートメントレベルの OPTION (RECOMPILE) を使用します
ランダムキーのテーブルでフィルファクターを無視する	フィルファクターを 70～80% に設定し、スケジュールに従って再構築します
不足インデックスの提案を無条件に作成する	書き込み時の負荷と既存インデックスとの重複を評価します
断片化の状態に関係なくすべてのインデックスを再構築する	1,000 ページ未満のテーブルと、断片化率が 5% 未満のインデックスは対象外にします
大規模テーブルで自動統計更新が実行されない	動的しきい値を利用するため、互換性レベルを 130 以上にします：MIN(500 + 0.20×n, SQRT(1000×n)) — 大規模テーブルでは約 20 倍早く実行されます
テーブル変数のカーディナリティが 1 行に固定される	遅延コンパイルを利用するため、互換性レベルを 150 にします
ハッシュ／ソートが tempdb にスピルする	メモリ許可サイズが適切になるよう統計情報を修正します。行モード MGF（2019 以降）では自動調整されます
デッドロック対策として NOLOCK を使用する	デッドロックにはロック順序の調整または RCSI が必要です — NOLOCK では防げません
1 回の大規模な DELETE によってログが肥大化する	WHILE ループ内で DELETE TOP (5000) を使って分割します

参考ファイル

• execution-plans.md — 実行プランの読み方、主要な演算子、カーディナリティ推定、CE バージョン対応表、インテリジェントなクエリ処理（IQP）、Query Store の構成と回帰の検出
• index-strategy.md — クラスター化キーの選択、カバリングインデックス、フィルター付きインデックス、断片化、不足しているインデックスの DMV
• wait-stats.md — 主な待機の種類、DMV クエリ、ベースラインの手法、リソースとの相関、TempDB の構成、サーバーの最大メモリ
• locking-blocking.md — 分離レベル、RCSI、ロックのエスカレーション、デッドロック、NOLOCK の危険性
• batch-operations.md — 分割した DML、最小限のログ記録、パーティションの切り替え、ログ管理
• statistics-tuning.md — ヒストグラムの解釈、自動更新のしきい値、昇順キーの問題、増分統計、フィルター付き統計