Module 6 Exercises: SQL Server Monitoring & Tooling Labs
5 guided labs, 3 challenge scenarios, and 2 break-it labs.
Guided Labs
Guided1. Enable Query Store on a test database and confirm its operation mode.
ALTER DATABASE YourTestDb SET QUERY_STORE = ON;
SELECT actual_state_desc FROM sys.database_query_store_options;Guided2. Run the same query twice with different parameter selectivity, find both plans in Query Store, and force the better one.
Guided3. Build a production-style Extended Events session filtered to duration > 2 seconds with a bounded rollover file target.
Guided4. Add Page Life Expectancy, Batch Requests/sec, and Full Scans/sec to a PerfMon data collector set.
Guided5. Read events back from an XEvents file target using sys.fn_xe_file_target_read_file.
Challenge Scenarios
Challenge6. A query performed well for months, then regressed after a deployment. Using Query Store, design a plan to find and force the pre-deployment plan without a code rollback.
Challenge7. Design an Extended Events session to specifically catch queries causing tempdb spills, tying back to Module 4’s SpillToTempDb signature.
Challenge8. A PerfMon dashboard shows Full Scans/sec climbing steadily over three months with no application changes. Propose which specific DMVs from this course you’d check next, in order.
Break-It Labs
Break-It9. Deliberately cause a query regression: force a bad plan via Query Store on a test query, observe degraded PerfMon/DMV metrics, then unforce it and confirm recovery.
Break-It10. Deliberately create Extended Events overhead: run a session with NO duration filter capturing every statement on a busy test workload, observe the file size/overhead, then fix it with an aggressive filter 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.