“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
Start Here
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
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
Common Questions
Where should I start when a query is slow?
With what the server is waiting on, not with the query text. The wait tells you whether you are looking at I/O, locking, CPU or a memory grant, and those four lead to completely different fixes. Troubleshoot RESOURCE_SEMAPHORE Waits, in Related Scripts below, covers that in full.
Are missing index recommendations safe to apply?
Treat them as evidence, not instructions. Each one is generated for a single query with no awareness of the indexes you already have or the write cost of another one. Read them next to index usage stats and consolidate before creating. Get Missing Indexes, in Related Scripts below, covers that in full.
What is an implicit conversion and why does it matter so much?
It is SQL Server silently converting a data type to compare two columns, and it usually stops an index being used. A query that should seek ends up scanning, and nothing in the plan shouts about it, so it hides in plain sight for years.
Does Query Store replace wait statistics?
No, they answer different questions. Query Store tells you which queries changed plan and regressed; wait statistics tell you what the server as a whole is short of. You want both.
See Also
This pillar is part of 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