execution-plan-analysis

実行プランの分析

このスキルを使って、SQL Server の実際または推定の実行プラン、特に .sqlplan ShowPlan XML をトリアージします。プランは判断の根拠として扱い、結論そのものとはみなしません。書き換えやインデックスを推奨する前に、スキーマ、データ型、インデックス、行数、パーティション分割、パラメーター値、ローカル実行とリンクサーバー経由の実行の違いを検証してください。

必要な入力

プランを検証するために必要な最小限の情報を尋ねます。

• SQL Server のバージョン、データベースの互換性レベル、Azure SQL と製品版 SQL Server のどちらか。
• 可能であれば実際のプラン。実行に危険がある場合は推定プラン。
• クエリテキスト、パラメーター値、関連する場合は SET オプション、実行時の症状。
• テーブルの DDL、列のデータ型、インデックス、統計情報の経過期間、パーティション構成と関数、行数。
• 参照されるオブジェクトがローカルのものか、リモートのリンクサーバーか、ビューか、テーブル値関数か、一時テーブルか。
• 許可される変更：クエリの書き換え、統計情報の更新、インデックスの追加・削除・変更、計算列、ステージングテーブル、またはコード変更なし。

入力情報の収集と仮定の状況の追跡には、../_shared/optimization-intake.md と ../_shared/assumption-tracker.md を使用してください。

ワークフロー

1. プランのコンテキストを確認する

プランが実際のものか推定のものかを記録します。実際のプランでは、実行時の行数、警告、スピル、メモリ許可の使用状況、実際のパーティションアクセスを確認できます。推定プランからもアクセスパス、変換、欠落インデックスの要求、結合方法は確認できますが、実行時の推定誤差やスピルを証明することはできません。

次の情報を記録します。

• ステートメントのサブツリーコストと最上位ステートメントのテキスト。
• コンパイル時のパラメーター値と、存在する場合は実行時のパラメーター値。
• 表示されている場合は、カーディナリティ推定器のバージョンと互換性レベル。
• 並列度、メモリ許可、警告。

2. コストの高い演算子を順位付けし、検証する

推定サブツリーコストまたは演算子コストで演算子を並べ替え、負荷の中心となっている可能性のある箇所を見つけます。ただし、割合だけを根拠にチューニングしないでください。小さなクエリの 90％を占める演算子は問題にならない場合があります。一方、入れ子になったループの分岐内で割合の低い演算子が繰り返されると、実行時間の大部分を占めることがあります。

候補となる演算子ごとに、次の情報を記録します。

根拠	記録する内容
演算子	物理演算と論理演算
オブジェクト	テーブル、インデックス、またはリモートソース
コスト	推定演算子コスト／サブツリーコストと相対割合
行数	推定行数、実際の行数、実行回数、読み取られた行数
述語	シーク述語、残余述語、プローブ、結合述語
警告	スピル、変換、欠落インデックス、結合述語なし、カーディナリティの問題

3. スキャン、シーク、残余述語を確認する

シークが必ずしも優れているとは限らず、スキャンが必ずしも悪いとは限りません。読み取られたデータ量と返されたデータ量を確認してください。

• インデックス／テーブルスキャン：大量の結果を返すクエリで想定されるものか、SARGable でない述語によるものか、インデックスの不足が原因か、型や照合順序の不一致が原因かを確認します。
• 残余述語を含むインデックスシーク：SeekPredicates と Predicate を調べます。数百万行を読み取ってから絞り込むシークでは、より適切なキー順序、計算列、フィルター選択されたインデックス、または書き換えが必要な場合があります。
• キー参照／RID 参照：実際の行数に実行回数を掛けます。参照が多数ある場合はカバリングインデックスが有効なことがありますが、幅広い INCLUDE 列をむやみに追加しないでください。

4. 暗黙的な変換と照合順序の問題を見つける

ShowPlan で CONVERT_IMPLICIT、PlanAffectingConvert、およびインデックス列の周辺にあるスカラー演算子を検索します。述語や結合の列側での変換を優先して確認してください。シークを妨げたり、推定を歪めたりする可能性があるためです。

各変換を次のように分類します。

• 投影のみを対象とする安全な変換。
• シークできる場合もあるが、推定に悪影響を与える述語の変換。
• 効率的なシークを妨げる列側の変換。
• スキャン、ハッシュ結合、またはリモートでの行単位処理を引き起こす結合キーの変換。

推奨する修正では、意味を維持してください。スキーマ変更が許可されている場合は、パラメーターの型、一時テーブルの型、リテラル、計算列、またはソース列の定義を一致させます。

5. 推定行数と実際の行数を比較する

推定値と実際の値に大きな誤差があると、誤った結合順序、メモリ許可、スピル、誤った結合アルゴリズムの原因を説明できることがあります。

次のいずれかに該当する演算子があれば、フラグを立てます。

• 実際の行数と推定行数に10倍以上の差がある。
• 実際の実行回数が予想を大幅に上回る。
• テーブル変数、複数ステートメント TVF、ローカル変数、古い統計情報により、推定が1行であるのに実際には多数の行がある。
• パラメーター スニッフィングまたは Parameter Sensitive Plan の挙動を示唆する、顕著な偏りがある。

推奨事項は根本原因に結び付けます。統計情報を更新する、フィルター選択性のある統計情報やインデックスを作成する、段階ごとのカーディナリティを得るために一時テーブルを使用する、パラメーター依存性に対処する、または述語を書き換えるなどです。

6. パーティションの排除を確認する

パーティション分割されたオブジェクトでは、実行プランでパーティションが排除されているかどうかを確認します。

次の点を確認します。

• パーティション分割列とデータ型。
• 述語の列と式の形式。
• 実際にアクセスされた、またはアクセスが推定されたパーティション。
• 変換、関数、OR 述語、結合、ビュー、ローカル変数によってパーティションキーが不明瞭になっていないか。

パーティション列を関数で囲む、型が一致しない値と比較する、信頼できる制約によって同等性が証明されない限り、パーティション分割列ではない関連する日付列で絞り込む、といった安全でないパーティション述語には注意を促します。

7. インデックスに関する警告を慎重に解釈する

不足インデックスに関する警告は、コンパイル済みの単一ステートメントに対する提案であり、設計そのものではありません。既存のインデックス、ワークロードのパターン、書き込みコスト、制約を比較してから、候補となるインデックスに反映します。

実行プランに更新のオーバーヘッドが示されている場合や、依頼にインデックスのメンテナンスが含まれている場合は、未使用または重複したインデックスも調べます。単一の実行プランだけを根拠にインデックスの削除を推奨してはなりません。ワークロードの証拠が必要です。

8. 証拠に基づいて調査結果を報告する

次の形式を使用します。

1. プランの概要：プランの種類、ステートメント、実行時の症状、主要な演算子。
2. 確認済みの事実：確認したスキーマ、インデックス、型、行数に関する事実。
3. 調査結果：各項目について、演算子、XML の証拠、影響、確信度を記載します。
4. 前提条件：確認済み、未確認、否定済み、または診断が必要として追跡します。
5. 推奨事項：最も安全なものから提示し、書き換え、インデックス／統計情報の変更、診断を分けて記載します。
6. 検証計画：収集する変更前／変更後のメトリック。

リファレンス

ShowPlan XML の属性、XPath 形式の参照、演算子コストの解釈については、references/showplan-xml-checklist.md を参照してください。