Four Ways of Answering “Why Is It Slow”
“The database is slow” is rarely one problem. Sometimes it’s a query, a bad plan, a stale statistic. Sometimes it’s blocking, and the fix is completely different depending on which one it actually is. Sometimes the DMV snapshot has already missed the evidence by the time anyone looks, and only a live trace would have caught it. And sometimes the honest answer is that nobody was watching at all, and the fix is to make sure that’s never true again.
Four pillars, twenty-eight scripts, one underlying job: turning “it feels slow” into a specific, evidenced cause.
Start Here
The Four Pillars
⚡ Query and Performance Tuning
What’s actually consuming resources, whether the optimizer has good information, and what’s blocked on I/O versus CPU.
🔒 Blocking and Locking
Who’s blocking whom, right now and historically, including the deadlock graph after SQL Server has already resolved it.
🔍 Extended Events Tracing
Capturing evidence live when the DMV snapshot isn’t enough, connections, procedure calls, and anything else the defaults don’t track.
📡 Collectors and Baseline Infrastructure
Standing SQL Agent jobs that collect blocking, wait stats, and capacity baselines before you know you need them.
An Escalating Toolkit, Not Four Unrelated Clusters
These four pillars map to four different moments, and the difference between them is when the evidence gets captured:
- Query and Performance Tuning and Blocking and Locking are on-demand. Something feels slow right now, you run a script, you get an answer from the current state of the server. This covers the large majority of real investigations.
- Extended Events Tracing is live capture for the cases on-demand can’t reach. A DMV snapshot only shows what’s true at the moment you query it. If the thing you’re chasing already happened, or happens intermittently, a standing trace is the only way to have caught it.
- Collectors and Baseline Infrastructure is the answer to “what if nobody was watching.” Rather than reaching for a tool after something goes wrong, the collectors write blocking chains, deadlocks, wait stats, and perfmon counters into history on a schedule, so the evidence already exists by the time anyone asks.
Read in that order, it’s a genuine escalation: reactive → live capture → standing infrastructure. Most days you only need the first two. The other two exist for the days you don’t.
How They Fit Together
- Something feels slow, right now. Start with Blocking and Locking to rule out active blocking first, it’s the faster check and a common false lead for “query tuning” problems that are actually blocking problems.
- Not blocking? Move to Query and Performance Tuning. Find what’s actually consuming CPU or I/O, check whether the optimizer has good statistics, and see what Query Store already knows about the query in question.
- The DMV snapshot already missed it, or it’s intermittent. That’s Extended Events Tracing, purpose-built traces for connections, procedure execution, or whatever specific evidence a one-off DMV query can’t hold onto.
- You want the evidence to already exist next time. Collectors and Baseline Infrastructure turns blocking, deadlocks, wait stats, and perfmon counters into standing history, plus a cross-cutting alert job that reads across all of it and surfaces CRITICAL/WARNING findings without anyone having to go looking.
Best Practices Across This Area
- Check blocking before query tuning, not after, a blocked session and a genuinely slow query look identical from the application’s side.
- Use Extended Events for anything that needs to be caught over time, not a one-shot DMV query re-run in a loop, the tracing engine is built for exactly this and is genuinely low-overhead.
- Install the standing collectors on any server you care about long-term, the value is having history from before you knew you needed it, not scrambling to set up tracing after an incident already happened.
- Cross-reference the collector alert job’s findings against the relevant on-demand script before acting, corroborated evidence beats a single automated flag.
Common Questions
Where do I start when someone says the server is slow?
Wait statistics, before touching anything else. They tell you which resource queries are queueing on, and each answer sends you somewhere completely different. Starting anywhere else means guessing which of five unrelated problems you have.
Most of my top waits look like background noise. Is that normal?
Entirely. SQL Server tracks dozens of wait types and most are idle background workers doing nothing interesting. The skill is filtering them out, which is why a benign-wait filter list matters more than the raw totals.
Is high CXPACKET a problem on its own?
Not by itself. Parallelism waits are normal on a server running parallel plans; they become a signal when paired with an unsuitable MAXDOP or a cost threshold left at the default of 5, which sends trivial queries parallel. Get MAXDOP Configuration, in Related Scripts below, covers that in full.
Should I trust missing index recommendations?
Treat them as evidence, not instructions. They are generated per query with no awareness of the indexes you already have or the write cost of adding another. Read them alongside index usage stats, and consolidate before creating.
What is the difference between a live look and historical wait stats?
Cumulative wait stats describe everything since the last restart, which is an average that can hide a sharp problem happening right now. For an active incident, look at what is running and waiting at this moment instead.
See Also
- DBA Scripts: The Complete Guide, the full pillar index
- Server and Configuration
- Maintenance and Automation
- SQL Server Wait Statistics (library)
Summary
Four pillars covering the full range of a performance investigation: on-demand diagnosis for the common case, live tracing for what a snapshot can’t catch, and standing collectors for when the honest answer needs to be “we already have the evidence,” not “we’ll set up monitoring now.” Start with blocking, then query tuning, then reach for tracing or the collectors as the situation demands more than a single snapshot can give you.
Leave a Reply