Troubleshooting THREADPOOL Waits In SQL Server With A Plan Cache Query

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!

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.