SQL Server Execution Plan Operators: Costing, Plan Shape, and What to Ignore
You’ve seen individual operators already (Seek, Scan, Key Lookup). Now let’s read a whole plan the way someone diagnosing a real incident actually does.
Cost Percentages: Useful, But Not the Whole Story
The operator showing “87% cost” is a reasonable place to start looking — but it’s computed from the optimizer’s row estimates, which you now know can be badly wrong under parameter sniffing or stale statistics. Cross-check cost % against actual row counts, not just at face value.
Reading Plan Shape, Not Just Individual Icons
Thick arrows between operators represent many rows flowing — trace these back to find where row counts balloon unexpectedly
Warning icons (yellow triangle) flag things like implicit conversions or missing statistics directly in the plan — don’t skip past these
Parallelism icons (yellow circle with arrows) show where the plan split across threads — useful context, not automatically good or bad
Estimated vs Actual: The Signal That Matters Most
-- Always use ACTUAL execution plan (Ctrl+M), not estimated — estimated never shows real row counts
SELECT * FROM dbo.OrderLog WHERE customer_id = 42;
Hover any operator and compare Estimated Number of Rows to Actual Number of Rows. A 10x+ gap anywhere in the plan is the single strongest signal something upstream (stale stats, a non-SARGable predicate, parameter sniffing) is misleading the optimizer — often more informative than the cost percentage itself.
Enjoyed this?
Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.
Finding Bottlenecks in SQL Server XML and Graphical Execution Plans
A 40-operator plan is hard to scan visually. Every graphical plan is backed by XML you can query directly — genuinely useful once plans get wide.
Getting the Raw XML
SET SHOWPLAN_XML ON;
GO
SELECT * FROM dbo.OrderLog o JOIN dbo.Customer c ON o.customer_id = c.customer_id;
GO
SET SHOWPLAN_XML OFF;
-- Or right-click a graphical plan in SSMS -> "Show Execution Plan XML"
Searching for Specific Problems
Once you have the XML, search (Ctrl+F in SSMS’s XML view, or programmatically) for these telltale strings:
Search for
Finds
PlanAffectingConvert
Implicit conversions that changed the plan — a direct hit for the SARGability issue from Module 3
NoJoinPredicate
An accidental CROSS JOIN — often a missing join condition bug
SpillToTempDb
A Hash Match or Sort that ran out of memory and spilled to disk — a serious performance red flag
ColumnsWithNoStatistics
Columns the optimizer had no statistics for at all
Finding This Programmatically Across the Plan Cache
This CROSS APPLY pattern is the exact same tool from the Developers & DBAs course’s window-functions chapter, now aimed at sys.dm_exec_cached_plans instead of a business table — the same skill, a new target.
SELECT TOP 20 qp.query_plan, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%SpillToTempDb%';
This finds every cached plan currently spilling to tempdb — a genuinely powerful way to proactively hunt for memory-pressure problems across an entire server, not just one query you’re already suspicious of.
Enjoyed this?
Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.
How to Read a SQL Server Execution Plan: Seeks, Scans, and Key Lookups
The last two lessons referenced execution plans repeatedly to prove their claims. Here’s how to actually read one systematically — the skill that turns “this query feels slow” into “this specific operator is the problem, for this specific reason.”
In SSMS, press Ctrl+M (Include Actual Execution Plan) before running a query.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT customer_id, order_status, order_total
FROM dbo.OrderLog
WHERE customer_id = 42;
The Key Plan Elements
Plan element
What it means
Index Seek
Good — navigated the B-tree directly to matching rows, exactly the root→branch→leaf path from Lesson 1
Index Scan
Read the entire index — fine on small tables, a red flag on huge ones for selective queries
Table Scan
No usable index existed at all — the engine has no B-tree to navigate
Key Lookup
Jump back to the clustered index per row — the exact problem Lesson 2’s covering index (INCLUDE) fixes
Read a plan right to left, top to bottom — the rightmost, deepest operators run first (typically the actual table/index access), feeding data up and left into operators that filter, join, and aggregate it, until the leftmost operator produces the final result.
The Single Most Useful Diagnostic Signal
The optimizer chooses its plan based on estimated row counts — when those estimates are badly wrong, it often picks a suboptimal plan (the wrong join type, an index skipped in favor of a scan, an inappropriate memory grant). STATISTICS IO reports logical reads per table, often a more stable, comparable metric across runs than wall-clock time, since wall-clock time is affected by whatever else the machine happens to be doing at that moment.
The Cost Percentage Trap
Common mistake: Treating an operator’s cost percentage (the big bold number SSMS shows under each operator) as a reliable measure of real-world expense. It’s derived from the same potentially-wrong estimates driving the whole plan — an operator estimated at 5% of a query’s cost can be the actual bottleneck if its underlying row estimate was badly off. Cross-check cost percentage against actual row counts (visible by hovering over each operator) before trusting it.
-- A parameter-sniffing-prone query worth trying: run once with a common value,
-- once with a rare one, and compare estimated vs actual rows on the same plan shape
SELECT * FROM dbo.OrderLog WHERE order_status = 'completed'; -- common
SELECT * FROM dbo.OrderLog WHERE order_status = 'cancelled'; -- rare
-- Different row counts naturally produce different (correct) estimates per query --
-- this becomes a real problem specifically inside a cached stored procedure plan,
-- covered in the Performance Tuning course
Practice tip: Run a query you already know is slow (or deliberately write one against a large table with no useful index), capture its actual execution plan, and walk it right to left identifying every Scan, Seek, and Lookup by name before looking at cost percentages at all. Building that habit — identify operators first, judge cost second — avoids the trap above.
Enjoyed this?
Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.
Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.
Module 4 Exercises: SQL Server Execution Plan Labs
5 guided labs, 3 challenge scenarios, and 2 break-it labs.
Guided Labs
Guided1. Capture an actual execution plan and note the Estimated vs Actual rows for every operator.
Guided2. Get the raw XML for a plan using SET SHOWPLAN_XML ON and locate the root RelOp element.
Guided3. Search a captured plan’s XML for the string “Warning” and interpret any results found.
Guided4. Query sys.dm_exec_cached_plans for the 10 plans with the highest total_worker_time (CPU) currently cached.
SELECT TOP 10 qs.total_worker_time, st.text
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;
Guided5. Force a sort spill by running an ORDER BY on a wide result set with a deliberately restricted memory grant (OPTION (MIN_GRANT_PERCENT/MAX_GRANT_PERCENT) if available, or a genuinely large sort), then find “SpillToTempDb” in the plan XML.
Challenge Scenarios
Challenge6. Given a 25-operator plan with no single operator above 20% cost, describe your approach to finding the real bottleneck (hint: it’s not always the highest-cost single operator).
Challenge7. A plan shows a Nested Loop with an outer row estimate of 10 but an actual of 2 million. Explain what this means and what you’d check next.
Challenge8. Write a query against sys.dm_exec_cached_plans that finds every currently-cached plan containing an accidental CROSS JOIN signature.
Break-It Labs
Break-It9. Deliberately write a query with a missing join condition (accidental CROSS JOIN) on two mid-sized tables, capture the plan, and confirm “NoJoinPredicate” appears in the XML.
Break-It10. Deliberately create a huge Estimated-vs-Actual gap: use OPTION (RECOMPILE) with a deliberately wrong local variable technique to defeat estimation, capture the resulting plan, then fix it and compare.
Enjoyed this?
Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.
10 questions on reading plans like a diagnostician, not just a spectator.
1. Why can an operator’s “cost %” in a plan be misleading?
A) It’s always 100% accurate B) It’s computed from row ESTIMATES, which can be wrong (parameter sniffing, stale stats) C) SQL Server doesn’t actually compute cost D) Cost % only applies to INSERT statements
Show Answer
Answer: B
Cost % is pre-execution and estimate-based — exactly the same estimates that can be wrong per Module 3’s cardinality estimation lesson.
2. What should you always capture in SSMS, and why, when diagnosing a real slow query?
A) Estimated plan — it’s faster to view B) Actual plan (Ctrl+M) — estimated plans never show real row counts C) Neither matters D) Only the query text
Show Answer
Answer: B
The actual plan includes real row counts per operator, which is what lets you compare against estimates and spot the gap.
3. What does a large gap between Estimated and Actual rows on one operator most strongly suggest?
A) Nothing meaningful B) Something upstream (stale stats, non-SARGable predicate, parameter sniffing) is misleading the optimizer C) A hardware failure D) The query is definitely correct
Show Answer
Answer: B
This is often the single strongest diagnostic signal in a plan — more informative than cost % alone.
4. What does a “thick arrow” between two operators in a graphical plan represent?
A) A faster operation B) A large number of rows flowing between those operators C) An error D) A parallel operation always
Show Answer
Answer: B
Arrow thickness is proportional to row count — trace thick arrows back to find where row counts unexpectedly balloon.
5. What does the XML string “PlanAffectingConvert” indicate when found in a plan?
A) A successful index seek B) An implicit data type conversion that affected the chosen plan C) A backup operation D) A parallelism warning only
Show Answer
Answer: B
This directly ties back to the SARGability-killing implicit conversions covered in Module 3.
6. What does “SpillToTempDb” in a plan’s XML indicate?
A) A successful query B) A Hash Match or Sort ran out of memory and spilled to disk — a serious performance flag C) A backup is running D) tempdb is corrupted
Show Answer
Answer: B
Spills mean the memory grant wasn’t enough for the operation, forcing much slower disk-based processing.
7. What does “NoJoinPredicate” in plan XML often indicate?
A) A well-optimized join B) An accidental CROSS JOIN, often from a missing join condition C) A missing index only D) A columnstore index
Show Answer
Answer: B
This is a common accidental-bug signature — a forgotten or mistyped join condition producing a full Cartesian product.
8. How can you search the plan cache for every currently-cached plan that spills to tempdb?
A) It’s not possible B) CROSS APPLY sys.dm_exec_query_plan() and search the XML text for ‘SpillToTempDb’ C) Only via SQL Server Profiler D) Only by restarting the server
Show Answer
Answer: B
Casting the query_plan XML to text and searching it across sys.dm_exec_cached_plans lets you proactively hunt server-wide, not just query-by-query.
9. Are yellow warning triangles in a graphical plan safe to ignore if the query “seems fine”?
A) Yes, always B) No — they flag real issues like implicit conversions or missing statistics worth investigating C) They only appear on errors D) They mean the query failed
Show Answer
Answer: B
Warning icons are the plan actively telling you something is off — don’t dismiss them just because the query technically returned results.
10. Why is SET SHOWPLAN_XML useful beyond just viewing the graphical plan?
A) It isn’t useful B) It exposes the raw plan structure so you can search/query it programmatically across many plans C) It only works on SELECT statements D) It replaces the need for indexes
Show Answer
Answer: B
Wide plans are hard to scan visually — the underlying XML lets you search for specific warning strings directly, or automate the search across the whole plan cache.
Enjoyed this?
Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.