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.

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 *