Our team fights SQL performance fires with heroics and guesswork, someone adds an index, someone restarts something, sometimes it helps. I want a repeatable methodology instead.
How do experienced people structure a performance investigation start to finish?
The difference between heroics and methodology is four disciplines wrapped around the same tools everyone already has. The structure experienced people follow, whether they articulate it or not:
Baseline before trouble: capture what normal looks like while things are fine, a scheduled snapshot of wait stats, the top queries by CPU and reads, key perfmon counters and Query Store enabled where the version allows. An incident investigated against a baseline starts with what changed, an incident without one starts with archaeology and the difference is usually the whole afternoon. This is the discipline teams skip because it pays nothing on quiet days, which is exactly when it must be built.
Define before measuring: slow is not a finding, the methodology starts by pinning what is slow for whom since when, one report or every screen, all day or at 9am, after the deployment or gradually. The answers pick the instrument, instance wide points at the server level checks in the thread linked alongside, one workload points at the DMV sequence in its sibling thread and since 9am points at what happened at 9am, half of defined problems confess during definition.
One change, then measure: the hypothesis names a cause the evidence supports, the change tests exactly that hypothesis and the measurement afterwards compares against the baseline. The heroic pattern, index added plus service restarted plus setting changed in one desperate hour, occasionally works and teaches nothing, since nobody knows which change did what and the untested two changes stay in production as mysteries. Discipline here feels slow and is faster, the fix that sticks beats three fixes that might have.
Write it down where the next fire can find it: what was measured, what was hypothesized, what changed and what resulted, a runbook entry per incident. Two seasons of this and the team owns a pattern library of its own system's failures, the 9am slowness turns out to be the same statistics timing three times running and the methodology's real product appears, fires that end at recognition rather than investigation.
Baseline job built this week, and the first incident under the new discipline was defined into confession in an hour, a deployment's missing index, one change, measured, documented. The team grumbled about the writing part exactly once, then reread the entry during the next fire.