Module 6 Quiz: SQL Server Monitoring & Tooling
1. How does Query Store differ from the plan cache?
A) They are identical
B) Query Store persists history per-database across restarts; the plan cache resets on restart
C) Query Store only works on Azure
D) The plan cache is more detailed
Show Answer
Answer: B
This persistence is exactly what makes Query Store useful for tracking regression over time, not just current state.
2. What does sp_query_store_force_plan let you do?
A) Delete a query from history
B) Pin a specific known-good plan for a query, without changing code
C) Force a full index rebuild
D) Disable Query Store
Show Answer
Answer: B
This is a direct, reversible, production-safe response to parameter sniffing regressions.
3. Why should a production Extended Events session use an aggressive WHERE duration filter?
A) It’s not necessary
B) To capture only what actually matters and keep overhead/disk usage low
C) Filters are required by SQL Server syntax
D) It has no effect on overhead
Show Answer
Answer: B
Unfiltered capture on a busy server generates enormous volume — filtering aggressively is the difference between diagnostic and production-safe.
4. What does EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS accomplish?
A) Guarantees zero events are ever lost
B) Prioritizes server performance over perfect event capture completeness
C) Disables the session entirely
D) Doubles the memory buffer
Show Answer
Answer: B
This ensures monitoring itself never becomes a bottleneck — an intentional trade-off for production sessions.
5. What does a low Page Life Expectancy counter typically indicate?
A) Excellent buffer pool health
B) Buffer pool pressure — pages being evicted and re-read from disk quickly
C) A CPU bottleneck only
D) A network issue
Show Answer
Answer: B
This ties directly back to Module 1’s buffer pool concept — low PLE means data isn’t staying cached.
6. A rising Compilations/sec relative to Batch Requests/sec often signals what?
A) Excellent plan reuse
B) Excessive recompiling, often from ad-hoc (non-parameterized) query bloat
C) A hardware failure
D) Normal, healthy behavior always
Show Answer
Answer: B
This connects back to Module 1’s plan cache bloat discussion — too many unique ad-hoc statements compiling fresh plans.
7. A rising Full Scans/sec trend alongside a stable workload most likely points to what?
A) Everything is fine
B) Missing or degraded indexes (fragmentation, stale stats)
C) A network slowdown
D) Increased RAM
Show Answer
Answer: B
A trend change with stable workload is a strong signal something structural (Module 2 territory) has degraded.
8. Which tool is best suited for answering “how has this specific query’s performance evolved over the past month”?
A) sys.dm_os_waiting_tasks (live-only)
B) Query Store
C) PerfMon alone
D) The plan cache alone
Show Answer
Answer: B
Query Store’s whole design purpose is persisted, per-query historical tracking — exactly this question shape.
9. Which tool is best for answering “what is currently blocking session 55, right now”?
A) Query Store
B) sys.dm_os_waiting_tasks
C) PerfMon historical logs
D) sys.dm_exec_query_stats
Show Answer
Answer: B
This is a live, real-time question — the DMV showing current wait state is the right tool, not a historical aggregate.
10. Why is combining multiple monitoring tools (not relying on just one) the recommended approach?
A) It’s unnecessary, one tool does everything
B) Each tool answers a different question shape — trend over time, current state, specific events, or per-query history
C) More tools always means more accuracy regardless of fit
D) This is not actually recommended
Show Answer
Answer: B
PerfMon (trend), DMVs (current/historical state), XEvents (specific captured events), Query Store (per-query history) are complementary, not redundant.
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.