Part of the DBA-Tools Project.
“The database is slow” almost never means the whole database, it means one query, one plan, one statistic that went stale at the wrong moment. Eight scripts on this site cover the full path from “something feels slow” to a specific, fixable cause: which queries are actually consuming the resources, whether the optimizer has good information to work with, whether it’s blocked on I/O or CPU capacity, and what Query Store already knows about the queries you’re investigating.
This post is the map. It groups them by what they diagnose and lays out a sensible order for a performance investigation rather than jumping straight to the first script that comes to mind.
Why Query and Performance Tuning Matters
- Most performance problems trace back to a small number of queries, finding which ones matters more than any server-wide tuning knob
- The query optimizer makes its decisions based on statistics, implicit conversions and stale statistics both quietly defeat good index design without ever throwing an error
- Query Store already captures plan history and regressions automatically once enabled, most of the answer is often already sitting there unread
- Wait statistics and worker thread pressure tell you what category of problem you have (I/O, CPU, memory, locking) before you spend time chasing the wrong one
The Scripts, Grouped by What They Diagnose
Which queries are actually the problem?
- Long-Running Queries, currently executing queries that have been running longer than expected, the fastest way to catch a problem while it’s happening
- Database IO Usage, which databases are actually generating the I/O load right now, the first split when the instance as a whole looks I/O-bound
Is the optimizer working with good information?
- Statistics Health, stale, low-sample, and never-updated statistics, with the exact
UPDATE STATISTICScommand to fix each one - Implicit Conversions, queries silently defeating index seeks because a comparison forces a data type conversion, invisible unless you specifically check for it
What does Query Store already know?
- Query Store Status, confirms Query Store is actually enabled and in read-write mode before trusting any of its data
- Query Store Top Queries, the queries Query Store has already identified as the heaviest, ranked and ready, no extra tracing needed
- Query Store Regressions and Forced Plans, what changed against a query’s own baseline today, and whether a deliberately forced plan is still working
What’s the server-level bottleneck?
- Wait Statistics, what the instance has spent its time waiting on, the category-level signal that points every other script in the right direction
- Worker Threads and Active Sessions, whether the instance is running out of the schedulable worker threads it needs to serve requests at all
How They Fit Together
A sensible order for a performance investigation, top-down rather than picking at random:
- Start at the server level with Wait Statistics, it tells you what category of problem you actually have, before you spend time on scripts that assume the wrong one
- Check for a threads or capacity ceiling with Worker Threads and Active Sessions, a server genuinely out of worker threads needs a different fix than a single slow query does
- Find the specific queries with Long-Running Queries (if something is running right now) or Database IO Usage (if the load is more diffuse across a database)
- Check what Query Store already knows with Query Store Status first, then Query Store Top Queries, often the heaviest queries are already ranked and waiting to be read
- Check whether the optimizer has what it needs with Statistics Health and Implicit Conversions, both are silent, no-error problems that only surface as “the plan looks wrong for no obvious reason”
Best Practices Across the Series
- Start with wait statistics, not a specific query, jumping straight to “which query is slow” without first confirming the category of bottleneck wastes time chasing symptoms
- Enable Query Store on every production database if it isn’t already, most of what these scripts need is captured automatically once it’s on
- Treat implicit conversions and stale statistics as routine health checks, not just something to investigate after a complaint, both degrade quietly with no error to flag them
- Re-check worker thread and wait statistics after any significant workload change, a fix for one bottleneck can simply reveal the next one underneath it
See Also
This pillar is one of ten in DBA Scripts: The Complete Guide, the map across the whole series organized by the question you’re actually asking.
Summary
Performance problems are rarely as vague as “the database is slow”, they’re a specific query, a specific stale statistic, a specific server-level resource running short. These eight scripts turn that vague complaint into a specific, fixable finding, starting from the server-wide wait picture and narrowing down to the exact query and the exact reason the optimizer chose badly.
Start with Wait Statistics if you haven’t already, it’s the fastest way to know which of the other seven scripts is actually worth running next.
Leave a Reply