Oh Baby Another Series
Don’t you love lists? I love lists. They’re so nice and tidy, like a RegEx. Just the other night I was thinking how nice it’d be to write a RegeEx to detect RegEx patterns.
I didn’t sleep.
Anyway, SQL Server. Things. Things running slowly. What’s up with that?
In these posts, I’ll walk through some common scenarios, and how to look for them.
It might even be in the EXACT SAME VERY ORDER I LOOK FOR THEM!
Put your money away.
Reason 1: The Server Is Overloaded
If you go to open a new tab in SSMS and you hear a creaking sound, it might be time to revisit some of your hardware choices.
But that’s a problem for future you. Your problem is right now.
- How to check it: sp_PressureDetector
- What to look for: Stuck queries, Resource Usage, Wait Stats
By default, it’ll look at both CPU and Memory counters. If you don’t know what’s going on, just hit F5.
If you’re hitting memory limits, things will look like this:
Some queries will have requested memory, but it won’t have been granted.
Waiting queries will be stuck in a queue, waiting on RESOURCE_SEMAPHORE.
A significant amount of memory will have been granted out, and available memory will be on the lower side. You’ll also probably see the waiter_count column
If you’re hitting CPU limits, things will look like this:
Available threads might be a negative, or very low number. Requests may be waiting on threads, and the number will pretty closely match the number of rows that are…
Waiting in the pool.
This’ll also show you queries that are running, and which ones are using the most threads.
Can You Fix It Quickly?
Maybe, maybe not. If you’re allowed to kill off queries, you might be able to right the ship now. If not, you’re stuck waiting for queries to finish and give back their resources.
Longer term, you have a long checklist to go through, including asking tough questions about hardware, settings memory and parallelism settings, and your queries and indexes. This script will give you a lot of information about what’s going on. It’s up to you to figure out why.
If you need help with this sort of thing, drop me a line.
Thanks for reading!