The Evidence-First Workflow: How to Actually Approach SQL Server Performance Tuning

This course assumes you already know T-SQL well — SELECT, JOIN, indexes as objects, stored procedures — and is entirely about the layer above that: how to diagnose why something is slow, with evidence, rather than guessing. Before any module ahead, one methodology underlies all of them. “It feels slow, let’s add an index” is how performance tuning goes wrong — it’s the exact folklore-over-measurement failure mode the Bonus module’s Myth-Busting lesson catalogs in depth. Here’s the loop that actually works, and the one you’ll apply in every module ahead.

The Five-Step Loop

1. Define Baseline

Measure duration, CPU, and I/O before touching anything. Without a number to compare against, “it’s faster now” is a feeling, not evidence. This means running SET STATISTICS IO, TIME ON (or capturing the same via Query Store) against the current, unmodified state, and writing the numbers down somewhere you’ll actually compare against later — not just “remembering it felt slow.”

2. Capture Evidence

Use Extended Events or Query Store (both covered fully in Module 6) — not guesswork, not “I think it’s the JOIN.” Actual captured data about what ran, how long, and what it waited on.

3. Identify the Bottleneck

Interpret execution plans and wait statistics (Module 4 and Module 5) to find the actual constraint — CPU-bound, I/O-bound, or lock-bound are three very different problems with three very different fixes. Treating a lock-bound query with a new index, for instance, accomplishes nothing, because the bottleneck was never about read efficiency in the first place.

4. Iterate: One Variable at a Time

Change one thing. Add the index, or rewrite the predicate, or update statistics — not all three at once. If you change three things and it gets faster, you’ve learned nothing about which one mattered, and you’ve built a false intuition (“adding indexes always helps”) that will mislead your next diagnosis.

5. Validate Against the Baseline

Re-run the same measurement from step 1. Did it actually improve, by how much, and did anything else get worse (writes slower because of a new index, for instance — the exact index-maintenance cost tradeoff Module 2 covers)?

Why This Discipline Matters

Performance tuning without this loop turns into superstition — “we always rebuild indexes weekly” without evidence that fragmentation was ever the problem for that specific table. Every module in this course — architecture, indexing, query optimization, execution plans, locking, monitoring, advanced features — applies this exact loop to a different layer of the system. Module 3’s “13-Hour Delete” case study is this workflow followed start to finish against a real, painful incident, and is worth returning to once you’ve absorbed all five steps here.