You Come And Go
When THREADPOOL strikes, even the best monitoring tools can have a bunch of blank spots hanging around in them.
If you’re on SQL Server 2016 or better, there are some helpful columns in sys.dm_exec_query_stats.
WITH threads AS ( SELECT TOP (10) deqs.sql_handle, deqs.plan_handle, deqs.total_reserved_threads, deqs.last_reserved_threads, deqs.min_reserved_threads, deqs.max_reserved_threads, deqs.total_used_threads, deqs.last_used_threads, deqs.min_used_threads, deqs.max_used_threads, deqs.execution_count FROM sys.dm_exec_query_stats AS deqs WHERE deqs.min_reserved_threads > 0 ORDER BY deqs.max_reserved_threads DESC ) SELECT t.execution_count, t.total_reserved_threads, t.last_reserved_threads, t.min_reserved_threads, t.max_reserved_threads, t.total_used_threads, t.last_used_threads, t.min_used_threads, t.max_used_threads, CASE WHEN (t.min_reserved_threads * 2) < t.max_reserved_threads THEN 'maybe' ELSE 'maybe not' END AS [sniffy?], d.query_plan FROM threads AS t CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS d ORDER BY t.execution_count DESC, t.max_used_threads DESC;
On My Laptop
Which doesn’t have anything terribly interesting on it at the moment, I get this back.
I have my results weighted towards what’s executed the most, then by how many threads they wanted to reserve.
This could give you a good idea about which queries contributed to THREADPOOL problems.
Keep in mind that, regardless of how many threads the queries end up using, they’ll reserve as many as they want (unless DOP is downgraded due to CPU pressure).
If they get blocked, they hold onto those threads.
If those threads aren’t available when they start running, they’ll either wait for them to show up, or run at a lower DOP.
What Should You Do Here?
- Take a look at the query plans: Are there any home dunk missing indexes?
- Take a look at the query text: Is there some obvious problem, like a non-SARGable predicate that you can fix?
- Take a look at your server: Are MAXDOP and CTFP set correctly?
- Take a look at the predicates: Is there some bad parameter sniffing going on (check the sniffy column for swings in threads)
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.