https://sqlmaster.mssqlplanner.com

Module 4 Exercises: SQL Server Execution Plan Labs (10 Hands-On Exercises)

Written by

admin

in

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.

📡 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.

More posts