Re-Run
There are a lot of posts about THREADPOOL on the internet, but what happens before your server threads go into the red?
You might see other signs of CPU contention — lots of waits on stuff like the CX waits, or SOS_SCHEDULER_YIELD.
But there are other signs, too, and they can show up in the lifecycle of a query.
Zombie Skeleton
The main states a query can be in are:
- Runing: Chugging happily along
- Runnable: Waiting for a CPU
- Suspended: Waiting on something else (locks, pages, whatever)
- Sleeping: Should probably disconnect maybe, I guess
If you’ve got a lot of queries that are runnable, they’re ready to run, but not getting CPU time.
There are a ton of reasons why you might see this rack up, like:
- Too few CPUs
- Totally untuned queries
- VM issues like noisy neighbors, CPU limits, etc.
- RBAR-style code
Good News!
Checks for this are coming to sp_BlitzFirst, and sp_PressureDetector.
If > 25% of your queries are runnable, we’ll warn you about it. What you do with that information is up to your skilled hands.
- For sp_PressureDetector, the change will be there by the time this post drops
- For sp_BlitzFirst, watch for the February release
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
Related Posts
- The How To Write SQL Server Queries Correctly Cheat Sheet: Conditional Join and Where Clauses
- Why Logical Reads Are A Bad Metric For Query Tuning In SQL Server
- What Else Happens When Queries Try To Compile In SQL Server: COMPILE LOCKS!
- Join me In Boston May 10 For A Full Day Of SQL Server Performance Tuning Training
Hi Eric, love your blogs and your sp_PressureDetector. Checked the Git for the latest update but the code is still says: SELECT @version = ‘1.10’, @versiondate = ‘20201001’;.
That is not the latest version, right?
Heh yeah, I forgot to bump that when I added that in. It’s fixed now. Thanks for letting me know.
Runing: Practicing the dark arts! 😉