Troubleshooting THREADPOOL With The Plan Cache

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.

Egg salad

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!



7 thoughts on “Troubleshooting THREADPOOL With The Plan Cache

  1. I am your blog regular reader. I enjoy your blogs a lot as they have deep meaning.

    Please here I coudnt get how (t.min_reserved_threads * 2) < t.max_reserved_threads points to Threadpool issue

    1. It doesn’t. It can help you figure out if there are big variations in the number of threads a query reserves.

      Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *