postmortem
1 TopicA Practical RCA Method for Azure SQL: Separating Evidence, Correlation and Root Cause
CPU hit 100%. You scaled the database. The problem went away. You still haven't proved that CPU was the root cause. We see RCAs written this way all the time, and it's easy to understand why. There's pressure to explain what happened, and a clear story feels better than an open question. But an RCA built on correlation can send a team in the wrong direction. You end up paying for capacity you don't need while the real cause waits for the next incident. Here's the method we use to keep evidence, correlation and conclusions apart. What an RCA should answer A useful RCA answers four questions: What happened? Symptoms and impact, with exact times. Why did it happen? The cause, backed by evidence. What stopped it? The mitigation. What are we still unsure about? The limits of the evidence. Most weak RCAs answer the first and third questions well and then treat the third as if it were the second. The framework Observation → Correlation → Hypothesis → Validation → Conclusion Step What it means Example Observation Something you measured directly "CPU reached 95% at 09:00 UTC" Correlation Two observations happened close together "Login failures started two minutes later" Hypothesis A possible explanation you can test "High CPU caused the failover" Validation Evidence that supports or rules out the hypothesis "Comparable CPU peaks last week caused no failover" Conclusion What the validated evidence allows you to say "CPU is not established as the cause" The step that gets skipped most often is validation, and it's the one that matters most. A worked example (synthetic) Here's an invented incident on a fictional database, SalesDB: 09:00 UTC CPU rises to 95% 09:02 UTC Login failures begin (error 40613) 09:04 UTC Database fails over to another replica 09:05 UTC Connections recover The first draft of the RCA practically writes itself: "High CPU caused the failover." Before you accept that, ask: Did CPU actually reach the limit? 95% is high, but it isn't exhaustion. Were worker or session limits reached? Check max_worker_percent and max_session_percent in sys.dm_db_resource_stats. Was memory under pressure? Was the failover triggered independently? For example, by planned maintenance. Check Resource Health and Service Health. What happened during earlier high-CPU periods? If CPU reached 95% three times last week with no failover, CPU alone doesn't explain this one. Did other databases on similar workloads see the same thing? Now classify every statement: Statement Classification CPU reached 95% Confirmed Login failures occurred Confirmed The events overlapped in time Confirmed CPU caused the failover Unconfirmed hypothesis The failover restored connectivity Confirmed The failover proves the old replica was faulty Not established That table is more honest than the first draft, and more useful too. It shows the reader exactly what's known and what still needs work. Habits that make RCAs better Use UTC everywhere. Mixed time zones are the most common reason two sets of logs "don't line up." Convert everything before you compare anything. Define the impact first. Who was affected, how badly, and for how long? "Some logins failed between 09:02 and 09:05" is far more useful than "the database was down." Keep observations and interpretation apart. "Error 40613 was returned" is an observation. "The database was unavailable because of load" is an interpretation. Write them in separate places. Consider more than one explanation. If you only have one hypothesis, you'll find evidence for it. Write down at least two, then look for evidence that would rule each one out. Compare against healthy periods. A metric only means something next to its baseline. High CPU during an incident matters less if CPU is just as high every Monday morning. Compare against unaffected resources. If two databases had the same load and only one failed, load probably isn't the whole story. Don't assume the fix explains the cause. Restarts, failovers and scale operations change many things at once: the compute node, connections, caches and sometimes the physical host. When the problem stops after one of them, you know something in that bundle mattered. You don't know which part. When "root cause unknown" is the right answer Sometimes the evidence doesn't support a definite cause. Saying so isn't failure. It's accuracy. A good RCA with an open cause still gives the reader something to act on: What was ruled out, and how. Which hypotheses remain, ranked by likelihood. What evidence would settle it next time, such as finer-grained monitoring, connection-level logging or a synthetic probe. What resilience work reduces the impact whatever the cause turns out to be, such as retry logic or alerting. A simple RCA template 1. Summary – One paragraph: what happened, the impact, the current status 2. Impact – Who/what was affected, start and end times in UTC 3. Timeline – Observations only, in UTC 4. Analysis – Hypotheses considered, evidence for and against each 5. Root cause – What the evidence supports (or "not established") 6. Mitigation – What restored service 7. Limitations – What the evidence could not show 8. Next actions – Prevention, detection, and evidence gaps to close Summary A good RCA tells you what the evidence shows, and also what it doesn't let you claim. "We restarted it and the problem went away" describes a mitigation, not automatically a root cause. Keep those two apart, and your RCAs become something people can trust and act on.