THREADPOOL
Published 08 March 2018
SQL Server assigns a scheduler to each CPU core to manage internal threads. Some of these threads are worker threads that are assigned to query requests on a one-to-one basis. If every worker thread is busy, additional requests can't be assigned straight away. SQL Server attempts to manage requests more efficiently by creating a pool of worker threads, so the next available worker thread from the pool will pick up the task. If every worker thread remains busy, SQL Server can't accept new requests and will hang.
THREADPOOL waits occur when a task is waiting to be assigned to a worker thread. This may happen because workers are tied up by large blocking chains, or there’s a large number of queries, many of which might have a high degree of parallelism.
Investigating
- To verify whether worker threads are busy, check the Avg. CPU queue length on the Analysis page for the number of threads waiting to be executed. Use the baseline feature to compare with earlier time ranges to find out what’s normal for your system.
- Check whether you're also suffering from high PAGELATCH_* waits. Long running batch executions may be reducing the number of worker threads available to other resources.
- Sort the Top queries by execution count and duration to find queries that are keeping these worker threads busy. You can then use your SQL Server Management Studio to check whether those queries have a high degree of parallelism (DOP), and consider reducing their frequency.
- Check your Alert Inbox for Blocking process alerts. The Alert details page tells you which process is blocked, and includes a fragment of the query that caused the block.
- Check the runnable_tasks_count of sys.dm_os_schedulers and compare it with the current_workers_count to verify that every worker is busy. If necessary, try reducing the runnable_tasks_count by tuning queries responsible for sustained CPU usage. See Optimizing SQL Server Query Performance (TechNet).
As a last resort and only if resource contention isn't the root cause, check the max worker threads setting (advanced option). The 0 default setting in SQL Server 2005 and later allows the server to calculate the optimal number of threads. If all else fails, entering a value specific to your system configuration may improve performance. See Configure the max worker threads Server Configuration Option (MSDN).