query-plan-analysis

Reading a SQL Server execution plan

A query plan tells you what SQL Server decided to do and, if it's an actual plan, what happened when it did. Most bad plan analysis comes from confusing those two things, or from reaching for the most visually obvious number rather than the one that means something.

Work through the triage order below. It exists to establish ground truth before you form an opinion, because almost every wrong conclusion about a plan comes from forming the opinion first.

Step 0: extract the plan before you read it

Never read a .sqlplan file directly into context, and never grep one.

• They are large. A trivial two-table join runs 120 KB; real plans run into megabytes. Reading one wastes your context and you will still miss things, because the interesting attributes are scattered across thousands of lines.
• They are usually UTF-16, so grep, rg, and friends silently match nothing. Finding no PlanAffectingConvert in a UTF-16 plan tells you nothing at all about whether the plan contains one.
• Some plans lie about their own encoding: SSMS writes UTF-16, but a plan that has been opened and re-saved is often UTF-8 bytes still declaring encoding="utf-16". Strict XML parsers reject those.

Run the bundled extractor, which handles all of this and prints a digest ordered to match the steps below. It lives at scripts/extract.py, in the same directory as this file. Resolve that to an absolute path and call it with one — your working directory is not the skill directory, and when this skill is installed as a plugin the skill directory is not anywhere you can guess:

python <this-skill-dir>/scripts/extract.py /path/to/plan.sqlplan

It needs only the Python standard library. Add --top 20 to widen the ranked sections on a large plan.

When you need more detail about one operator — its predicates, its seek keys, its per-thread numbers — do not open the raw XML. Ask the extractor:

python <this-skill-dir>/scripts/extract.py /path/to/plan.sqlplan --node 16