Module 4 Quiz: SQL Server Execution Plan Analysis
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.
📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook
Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.