Case Studies and Real-World Scenarios

Big Idea: The fastest way to become job-ready is to solve realistic scenarios. Most production issues are patterns.
If you practice these patterns, you will recognize them immediately in real life.

Recommended Session Duration

180–300 minutes (case walkthroughs + lab + discussion)

Prerequisites

• Comfort with the incident workflow taught in the previous lesson
• Basic knowledge of indexes, stats, blocking, and Query Store concepts
• Ability to read basic DMV outputs

1. Symptom: What users reported and what metrics show.
2. Impact: Business effect (timeouts, lost sales, SLA breach).
3. Evidence captured: waits, top queries, blocking chains, I/O latency, Query Store.
4. Diagnosis: the bottleneck and root cause.
5. Mitigation: quick action to restore service.
6. Permanent fix: changes to prevent recurrence.
7. Validation: before/after proof.
8. Prevention: monitoring, alerts, or policies added.

Symptom: Every weekday at 9:00 AM, the application slows down and then “freezes.”
Users report spinning loaders and timeouts. CPU is normal, but the database feels locked.

Impact: Customer checkouts fail. Support tickets spike. SLA breach from 9:00–9:15 AM.

Evidence captured: Most active requests have LCK_* waits, and many sessions show a non-zero blocking_session_id.
One session is the “root blocker” running a long UPDATE inside an explicit transaction.

Diagnosis: A scheduled job runs at 9:00 AM and updates a large table in one transaction.
It holds locks for too long and blocks many OLTP reads/writes.

Mitigation: Pause/reschedule the job outside peak hours. If approved, stop the job to restore service.

Permanent fix: Batch the UPDATE (small chunks + commits), add supporting indexes to reduce scanned rows,
and review isolation/read strategies to reduce lock conflicts.

Validation: Compare blocking counts and average transaction time before/after.
Confirm the job completes without creating a blocking chain.

Prevention: Create an alert for long blocking chains and set policy:
“Scheduled jobs cannot run during peak OLTP windows.”

Symptom: A monthly finance report takes 45 seconds instead of 5 seconds.
It used to run fast, but slowly became worse over months.

Impact: Reporting SLAs missed; team delays operational decisions; report time conflicts with business tasks.

Evidence captured: The query has extremely high logical reads and long elapsed time.
Execution plan shows a large table scan. Storage latency is normal; the query is simply reading too much.

Diagnosis: Data growth made an old query pattern expensive. Filtering columns are not supported by an index,
forcing a scan and huge reads.

Mitigation: Run the report off-hours until fixed, or add a temporary filtered approach if business allows.

Permanent fix: Create a targeted nonclustered index on the filter/join columns, include the needed select columns,
and verify the plan becomes seek-based. Update statistics after creating the index.

Validation: Compare runtime and logical reads before/after. Confirm report is stable across different date ranges.

Prevention: Add “top expensive queries by reads” review monthly; proactively index for high-impact reports.

Symptom: CPU spikes to 95% during peak hours. The same period shows slow app response time and rising timeouts.

Impact: API endpoints exceed SLA; customer experience degrades; system appears unstable.

Evidence captured: Top active requests show high cpu_time with relatively low physical reads.
The top query executes thousands of times and performs expensive per-row computations.

Diagnosis: “Chatty” workload + inefficient query pattern (repeated small queries or scalar functions)
causes CPU churn and poor scalability.

Mitigation: Reduce peak workload temporarily (app throttling / caching) while fixing root cause.

Permanent fix: Rewrite to set-based operations, remove per-row function calls, reduce repeated executions,
add appropriate indexes, and validate compilation patterns.

Validation: Compare CPU utilization, executions/sec, and endpoint latency before/after under peak simulation.

Prevention: Add “top queries by CPU” dashboard and review during release cycles.

Symptom: Some queries are fast, then suddenly slow with no obvious CPU spike.
During the slow time, multiple sessions wait on RESOURCE_SEMAPHORE.

Impact: Random timeouts, unpredictable performance, frustrated users and developers.

Evidence captured: Requests show RESOURCE_SEMAPHORE waits and large granted_query_memory requirements.
Execution plans show large sorts/hashes. Row estimates are far from reality.

Diagnosis: Poor cardinality estimation (stale stats or complex predicate patterns) caused huge memory grants.
The system queues queries waiting for memory.

Mitigation: Reschedule heavy reporting queries; reduce concurrency until root cause fixed.

Permanent fix: Update statistics (targeted), improve indexes to reduce sort/hash needs,
rewrite query to be more selective, and stabilize plan behavior.

Validation: Compare memory grant size, wait time, and query runtime before/after.

Prevention: Implement stats maintenance strategy and monitor memory grant waits proactively.

Symptom: After a new release, one endpoint becomes very slow. Rolling back the code does not fully fix it.
The same stored procedure is fast for some users and extremely slow for others.

Impact: SLA breach in production, and the team loses confidence in deployments.

Evidence captured: Query Store shows a plan change correlated with the time of deployment.
The slow plan performs large scans and hash joins; the old plan used seeks and nested loops for selective values.

Diagnosis: A plan regression occurred. Parameter sniffing and plan caching caused a plan optimized for one set
of parameter values to be reused for very different values, producing unstable performance.

Mitigation: Force the known good plan using Query Store (if available) to restore service quickly.

Permanent fix: Tune the stored procedure for stable performance across parameter ranges:
improve indexing/selectivity, update stats, and refactor logic to avoid extreme plan sensitivity.

Validation: Test across multiple parameter values, confirm stable latency, and verify plan stability over time.

Prevention: Add Query Store regression monitoring and require performance tests for critical endpoints before release.

Top currently running requests (elapsed time)

SELECT TOP (20)
  r.session_id,
  r.status,
  r.blocking_session_id,
  r.wait_type,
  r.cpu_time,
  r.total_elapsed_time,
  r.logical_reads,
  r.writes,
  DB_NAME(r.database_id) AS database_name,
  LEFT(t.text, 4000) AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

File I/O latency

SELECT
  DB_NAME(vfs.database_id) AS database_name,
  mf.physical_name,
  CASE WHEN vfs.num_of_reads = 0 THEN 0
       ELSE vfs.io_stall_read_ms / vfs.num_of_reads END AS avg_read_latency_ms,
  CASE WHEN vfs.num_of_writes = 0 THEN 0
       ELSE vfs.io_stall_write_ms / vfs.num_of_writes END AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
  ON vfs.database_id = mf.database_id
 AND vfs.file_id = mf.file_id
ORDER BY avg_read_latency_ms DESC;

Incident Report Template (Copy/Paste)

• Incident title:
• Date/time window:
• Symptom (measured):
• Business impact:
• Evidence captured: (3–5 bullet points)
• Diagnosis (root cause):
• Mitigation applied:
• Permanent fix plan:
• Validation proof:
• Prevention (monitoring/alerts/policy):
• Lessons learned:

Prompt:
“Which case study feels most realistic to you—and why? If you had only 10 minutes to stabilize production,
what mitigation would you choose first, and what evidence would you collect before making permanent changes?”