A developer sees a plan full of icons and asks which one is the problem. Explaining a query plan works when you connect the expensive work to the query’s purpose and the change the developer can make.
Start With the User’s Symptom
Before opening the plan, ask what is slow and when. A report that became slow after a deployment needs a different investigation from a query that has always been expensive. Record the exact statement, parameters, database, time window, and expected output. A plan without that context can lead to optimizing the wrong execution.
I begin by repeating the developer’s goal in plain words. Is the query meant to return one customer or a month’s orders? Which part feels slow to the user? That sets the scale for interpreting rows and operators. A plan is a model of execution, not a verdict on coding style.
Find the Work That Dominates
Look for large scans, repeated key lookups, sorts, hash operations, spills, and exchanges that carry many rows. Operator cost percentages in a graphical plan are optimizer estimates within that plan, not measured elapsed time for each icon. Use actual execution details and runtime measures where available. Focus on work that can explain the reported symptom.
I trace rows from the result toward the source. If the query returns ten rows after reading a large table, ask where reduction should have happened. A scan is not inherently bad. It can be correct for a broad request or small table. The question is whether the access path matches the data and predicate.
Compare Estimated and Actual Rows When Explaining a Query Plan
A large gap between estimated and actual rows can drive a poor join choice or memory grant. Look near the earliest operator where the gap appears. Statistics, parameter values, correlated columns, and expressions on predicates can affect estimates. A later mismatch can simply be the result of an earlier one. Do not treat every estimate difference as a crisis.
I show the developer the specific filter or join where the plan first became surprising. Which values were supplied for this execution? Test common and rare values if the procedure is parameterized. One captured plan can be excellent for one parameter and poor for another.
Tie the Finding to Query Text
Point to the WHERE, JOIN, GROUP BY, or ORDER BY clause that caused the work. A function around an indexed column can prevent a useful seek. A join with an incomplete key can multiply rows. A wide SELECT list can turn an otherwise covering index into many lookups. Explain the relationship without blaming the person who wrote the query.
The query below is a simple diagnostic wrapper. Run it on a test system or in an approved window and inspect its actual plan. The reported reads belong to the current server and data, not to a universal benchmark.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT OrderId, CustomerId, OrderDate
FROM dbo.Orders
WHERE CustomerId = 42
AND OrderDate >= '2025-01-01'
AND OrderDate < '2025-02-01'
ORDER BY OrderDate, OrderId;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;Offer One Testable Fix After Explaining a Query Plan
Suggest the smallest change that addresses the evidence: a more selective predicate, updated statistics, a useful index, or a query rewrite. State the tradeoff. An index consumes storage and adds write maintenance. A query rewrite can change NULL or duplicate behavior. The developer needs a proposed experiment and a verification query, not a demand to add every missing-index suggestion.
I compare output in both directions before calling a rewrite equivalent. This code shows the pattern with two prepared result tables. It checks values and keys, but duplicate handling needs its own review because EXCEPT uses distinct rows.
SELECT OrderId, TotalAmount
FROM dbo.OldResult
EXCEPT
SELECT OrderId, TotalAmount
FROM dbo.NewResult;
SELECT OrderId, TotalAmount
FROM dbo.NewResult
EXCEPT
SELECT OrderId, TotalAmount
FROM dbo.OldResult;Explaining a Query Plan in Their Terms
Say, “The filter leaves few orders, but the engine reads many before applying it,” rather than starting with an operator’s internal name. Then show the plan evidence. A developer can act on a predicate, join key, or selected column. They cannot change a colorful icon directly. Keep estimates, actuals, and measured duration separate in the explanation.
I write down the before and after plan identifier and test conditions. If the change helps one parameter and hurts another, report both. A successful tuning conversation ends with a decision about code and workload, not a debate over which icon looked largest.
Check the Full Workload
Test the candidate with representative parameters and concurrent use. A plan improvement in isolation can increase writes or block another path. Query Store can show whether the change remains helpful after deployment. Keep a rollback plan for the code or index. A plan can change again when statistics or data volume changes.
What would make you reject the proposed fix? I ask that before deployment. If the result set changes or a common case regresses, stop and revisit the hypothesis. The plan is evidence for a targeted decision, not a decoration for a postmortem.
A plan explanation starts with the query’s question and the data it sees. Identify the estimated and actual row counts, access method, join choice, sort or hash work and any warning that changes interpretation. I ask the developer what the expected result size is before discussing an operator. A large scan can be reasonable for a report that genuinely needs most rows, while a seek can still be expensive if it repeats millions of times.
Use the actual execution plan with representative parameters when safe, and compare it to runtime evidence such as reads and duration. Do not turn the plan’s percentage labels into a precise invoice for every operator. They are estimates within a plan, not a measured allocation of wall-clock time. When explaining a query plan, cover one observed mismatch and one testable remedy at a time.
After a change, check more than the single query. A new index adds write and maintenance cost, and a rewrite can change parameter behavior. The shared goal is a stable workload, not a screenshot with one green number.
Related reading on this blog: AI Execution Plan Analysis: I Gave It My Plan and Asked What Was Wrong and Execution Plans and Indexing Strategies: Quick Guide.
A query plan is not a picture to admire, it is evidence for a change a developer can test.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
