In parts 1 and 2 of this series, we’ve gathered info and done the triage just like anyone in almost any industry does
At this point you’ve:
- Defined what “slow” means and built a timeline (Part 1).
- Checked things outside SQL Server like network, storage, and VM noise (Part 2).
Now it’s time to open the hood on SQL Server itself.
Step 1: Check Active Sessions
Run a quick session (sp_whoisactive is a favorite):
- Who’s running queries right now?
- What queries have been running the longest? Is that normal?
- Any blocking chains?
- Are any queries hogging resources?
At this stage, you’re only identifying potential offenders. Next issue, we’ll dig into queries and indexes more deeply.
Step 2: Look at Wait Stats
Wait stats tell you what SQL Server has really been waiting for (everything in SQL Server is waiting for something else):
- PAGEIOLATCH: slow storage reads.
- LCK_M_X: blocking/locking.
- CXPACKET/CXCONSUMER: parallelism info.
- THREADPOOL: CPU threads
- RESOURCE_SEMAPHORE: memory
- ASYNC_NETWORK_IO: Probably more of a client side problem than SQL Side
- The most comprehensive list of wait types and explanations from my friends at SQL Skills
This isn’t about solving yet – it’s about categorizing where SQL feels the pain.
Step 3: Review Agent Jobs & Error Logs
SQL may already be waving red flags:
- Overlapping or stuck Agent jobs. A long running purge job or index rebuild can cause all sorts of issues during the day.
- Failed backups or CHECKDB runs. A failed CHECKDB could mean corruption. Read this
- Errors or memory dumps tied to patching or system instability. Look in the same folder as your ERRORLOG location
- Can’t find that folder? Watch this
Step 4: Don’t Forget the “Gotchas”
Other less obvious issues can cause system-wide drag:
- High VLF count (often from failed or missing log backups).
- Database compatibility or config changes – check SSMS reports like:
- Server level: Configuration Changes History
- Database level: All Blocking Transactions, Index Usage Statistics
- Recent patching issues (especially if tied to errors or dump files).
These aren’t everyday culprits, but when they show up, they can cripple performance.
Step 5: Compare Against Your Baseline
Today’s “slow” may be tomorrow’s “normal.”
- Track batch requests/sec, CPU Utilization, wait stats, I/O latency, and log file size/VLF count.
- Without this baseline, every slowdown feels like a brand-new mystery.
If you don’t already have a baseline, NOW is the time to start, while the server is healthy.
- Collect I/O stats and wait stats regularly.
- Run sp_Blitz for a full health snapshot (free tool from Brent Ozar)
- Capture DMV performance counters (sys.dm_os_performance_counters) on a schedule.
A baseline doesn’t need to be fancy, it just needs to exist so you know what “normal” looks like before things go sideways.
The Bottom Line
Part 3 is about categorizing slowness inside SQL Server: sessions, waits, jobs, error logs, and configuration gotchas. Don’t jump straight into query rewrites yet. You’re still isolating the nature of the slowdown. Having a consistent process for this reduces panic and anxiety.
In Part 4, we’ll cover what to do when the culprit is truly inside SQL Server: look at queries, indexes, and design choices.
Thanks for reading!
