Part of the DBA-Tools Project.
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.
The Four Pillars
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.
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