Memory hook: Find the wait, inspect the plan and change the smallest proven cause.
Must remember
- Establish a baseline for CPU, I/O, memory, waits, duration and throughput. Database watcher, Azure Monitor, Extended Events, Query Store and DMVs expose different evidence. Capture a representative window; a quiet average can hide a peak problem.
- Query Store retains supported query/plan/runtime history and can reveal regressions. Actual/estimated execution plans show access methods, joins and estimates; compare estimated versus actual rows. Stale statistics, skew and parameter sensitivity can produce poor choices.
- Blocking means sessions wait for incompatible locks; a deadlock is a cycle requiring a victim. Shorten transactions, use appropriate indexes/access order and inspect isolation requirements. Killing a session without understanding rollback can prolong disruption.
- Indexes can reduce reads but add write, storage and maintenance cost. Statistics support estimates; index/statistics maintenance should follow evidence. Sargable predicates help use suitable indexes; excessive sorts, spills or scans suggest query/data-design issues.
- Integrity checks detect corruption; they do not repair all damage automatically. Automatic tuning and intelligent query processing help supported scenarios but need monitoring. Database-scoped settings and Resource Governor have platform-specific scope; not every SQL offering exposes the same instance controls.
- Scale compute/storage only after identifying saturation. Compare cost per useful transaction and tail latency after a change. Keep a rollback for forced plans or configuration changes and verify concurrent workload behaviour.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| A previously fast query suddenly regresses | Compare Query Store plans/runtime and recent changes. |
| High duration with lock waits | Investigate blocking and transaction scope. |
| Large estimate-versus-actual row difference | Inspect statistics, skew and query predicates. |
Traps
- An index recommendation is not a free improvement.
- Blocking and deadlocks are different.
- Scaling CPU cannot remove every lock or query-design problem.
Active recall
1. What does Query Store add beyond a current plan?
Historical query/plan/runtime evidence for comparisons and regression analysis.
2. Why inspect waits?
They reveal what work is waiting on rather than merely showing that it is slow.
3. Why can too many indexes hurt?
Every relevant write may update more structures and consume storage/maintenance effort.
4. What does an integrity check test?
Supported consistency of database structures/data, not business correctness.
5. Why validate a forced plan later?
Data and workload changes can make the previously good plan inappropriate.