Execution plans show how SQL Server actually chose to execute your query.
Complete 12 of 24 practices (50%) and enter your name to unlock the Certificate of Participation.
An execution plan is a data-flow story. Operators consume rows, transform them and pass them onward. Read the plan to understand the path from access method to joins, sorts, aggregates and final output.
Instead of asking 'Which icon is bad?', ask: 'Where did the row count explode? Where did SQL Server scan more than expected? Where did it sort or spool because the chosen shape demanded it?'
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT ...
FROM ...
WHERE ...;
-- Include Actual Execution Plan in SSMS.
-- Compare plan shape + logical reads + elapsed time.Execution plans become useful when you connect the picture to rows, reads, time and business intent.
The optimizer chooses a plan before the query runs, so it must estimate how many rows each operator will process. When those estimates are wrong, the chosen join type, memory grant or access method may also be wrong.
If SQL Server expects 10 rows but receives 10 million, a nested loops plan that looked cheap on paper may become painfully expensive at runtime.
-- In the Actual Execution Plan, inspect:
Estimated Number of Rows
Actual Number of Rows
Actual Number of Rows for All Executions
Estimated Number of Executions
Actual Number of Executions
-- Large gaps often matter more
-- than the operator icon itself.A plan problem is often an estimation problem before it becomes an operator problem.
A scan is not automatically a problem, and a seek is not automatically good. The right access method depends on selectivity, table size, row goals, covering columns and how many rows the rest of the plan needs.
A scan over 20 rows may be cheaper than a seek plus thousands of key lookups. A seek over half the table can still be expensive.
-- Selective predicate may favor a seek:
SELECT *
FROM dbo.Orders
WHERE CustomerID = 12345;
-- Broad predicate may reasonably favor a scan:
SELECT *
FROM dbo.Orders
WHERE OrderDate >= '2020-01-01';
-- Validate with actual reads and row counts.The goal is not 'make every scan a seek'; the goal is 'make the access method fit the amount of data required.'
SQL Server chooses physical join algorithms based on estimated row counts, available order, indexes and cost. A join operator is not good or bad by itself; it is good or bad for the row volumes and access paths around it.
An INNER JOIN in SQL text could become Nested Loops, Hash Match or Merge Join. The optimizer chooses based on its estimate of the cheapest physical strategy.
SELECT o.OrderID,
c.CustomerName
FROM dbo.Orders AS o
JOIN dbo.Customers AS c
ON c.CustomerID = o.CustomerID;
-- Physical operator could be:
-- Nested Loops
-- Hash Match
-- Merge Join
-- Inspect row estimates and indexes.Do not tune by forcing a join type first; understand why the optimizer chose it and whether the surrounding estimates were correct.
A Key Lookup is not automatically bad. It becomes suspicious when a nonclustered seek returns many rows and SQL Server must perform a lookup for each one to retrieve missing columns.
The operator tooltip may look inexpensive per execution, but repeated executions can dominate logical reads and elapsed time.
-- Existing index:
CREATE INDEX IX_Orders_CustomerID
ON dbo.Orders(CustomerID);
-- Query also needs OrderDate and Amount.
-- Possible covering version:
CREATE INDEX IX_Orders_CustomerID
ON dbo.Orders(CustomerID)
INCLUDE (OrderDate, Amount);
-- Validate reads before and after.A covering index is a tradeoff, not a trophy: remove expensive lookups only when the workload justifies the extra index width.
Some operators need workspace memory before they run. SQL Server grants memory based on estimates. If the grant is too small, Sort or Hash operators may spill to tempdb. If it is far too large, one query can reserve memory that other queries need.
A plan estimated for 5,000 rows may receive 5 million rows. The Sort now needs far more memory than the optimizer granted, so intermediate runs spill to tempdb.
-- In the Actual Plan inspect:
Memory Grant Info
Warnings
Spill Level
Actual Rebinds / Rewinds
Actual Rows
Estimated Rows
-- Also measure:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;A spill is evidence of extra work; the real tuning question is why the operator needed more memory than SQL Server expected.
Some plan operators exist because the optimizer is protecting itself from repeated work or preserving correctness. Spools, exchanges and warning icons are clues, not automatic proof of a bad plan.
A spool may be saving the query from rescanning an expensive subtree. But if a huge spool is built repeatedly, the underlying join or predicate design may deserve attention.
Plan clues:
- Table Spool / Index Spool
- Sort / Hash spill warning
- Implicit conversion warning
- Missing join predicate warning
- Large row-estimate mismatch
- Huge Actual Number of Executions
- Unexpected parallel exchanges
Clue != root cause.
Trace the row flow.The plan is evidence. Tune the reason the operator exists, not the icon itself.
The execution plan is a diagnostic tool, not the finish line. A disciplined tuning workflow begins with a baseline, explains where work is happening, changes one thing at a time, and verifies improvement under representative conditions.
A plan with a scan may be excellent. A plan full of seeks may still be terrible. The proof is in reads, CPU, elapsed time, row counts and correctness.
1. Reproduce the slow query safely
2. Capture Actual Execution Plan
3. Record STATISTICS IO / TIME
4. Find large row-estimate gaps
5. Inspect access methods and joins
6. Look for repeated lookups / spills / warnings
7. Form a root-cause hypothesis
8. Make one targeted change
9. Re-run under comparable conditions
10. Confirm result correctness
11. Keep the change only if the evidence improvesThe best execution-plan skill is not recognizing icons; it is building and testing a correct explanation of where the work comes from.
Open each item after answering it in your own words. The 24 interactive practices above drive certificate progress.
The row-flow story: where rows come from, how many are expected and actual, how they are transformed, and where work multiplies.
Because the optimizer chooses joins, memory grants and access methods using estimated cardinality.
No. A scan can be the cheapest strategy when many rows are needed or the object is small.
A lookup may execute once per qualifying row, multiplying access work.
Compare runtime evidence and correctness before and after under comparable conditions.
| Question | What to inspect |
|---|---|
| Did SQL Server estimate correctly? | Estimated Rows vs Actual Rows |
| How much data was read? | STATISTICS IO + access operators |
| Did work multiply? | Actual Executions + join row flow |
| Was extra workspace needed? | Sort / Hash / memory grants / spills |
| Did the fix really help? | Before/after reads, time, CPU and correctness |
Complete at least 12 of the 24 practice cases (50%) and enter your name.
Execution plans are diagnostic evidence, not a scorecard.