THREADPOOL

(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)

(Back to main page…)

Description:

This wait type is when there are no available threads in the server’s thread pool, which can lead to login failures or queries not being able to run.

(Books Online description: “Occurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are taking unusually long, thus reducing the number of workers available to satisfy other batches.”)

Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.

Added in SQL Server version:

Pre-2005/2005

Removed in SQL Server version:

N/A

Extended Events wait_type value:

Maps to the SOS_WORKER map_value in sys.dm_xe_map_values (thanks to Jonathan’s post here).

The map_key value in sys.dm_xe_map_values is 113 in 2008 and 2008 R2, and 117 in 2012 and 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.

Other information:

SQL Server creates a certain number of worker threads when the instance starts, including some for background tasks and a small number to populate the thread pool. The thread pool will grow and shrink dynamically as required, up to the max worker threads for the instance. For instance, on my laptop with 8 logical processor cores, there can be up to 576 worker threads. You can see how many threads are currently exist in your instance using select count (*) from sys.dm_os_workers and you can see the possible maximum using select max_workers_count from sys.dm_os_sys_info. For more information on the number of worker threads SQL Server will create, see this Books Online entry.

When a query is going to execute, SQL Server determines how many threads are required (see this blog post by Paul White for details) and reserves that many threads from the thread pool, or creates the required number of threads, as long as that doesn’t exceed the max worker threads. If there are not enough available threads, then THREADPOOL waits occur. If there are no available threads at all, connections to SQL Server will also fail.

This means that on an instance that suddenly requires more threads than are available in the thread pool, you will see some short-duration THREADPOOL waits, and this is normal. When there are no available threads at all, this is worker thread starvation, and is not normal.

Worker thread starvation can occur for a number of reasons, including:

  • A thread acquiring a lock which then blocks all other threads, more connections happen and are blocked, eventually exhausting the thread pool
    • Look for nearly all the rows in the output from sys.dm_os_waiting_tasks (using my script) being blocked by a single SPID, and consider killing it
  • A parallel query plan is being executed by many hundreds of connections, exhausting the thread pool
    • See CXPACKET wait for how to identify parallel plans and possibly reduce the amount of parallelism happening
  • A query plan is being executed by many connections and the query is taking longer than usual to run, exhausting the thread pool
    • See CXPACKET wait for how to identify skewed parallelism
    • Also look for long-running queries and investigate what waits are occurring to see if there’s a general performance problem that’s leading to thread starvation, or whether the long-running queries have incorrect query plans for some reason
  • Number of active sessions in SQL Server equal to the number of worker threads
    • Check the number of rows in sys.dm_exec_requests. If the number is around the number of worker threads, decrease the number of connections or increase the max worker threads value (after consulting with someone who knows the possible side-effects of doing so for your workload). Note that it can be entirely normal for there to be many more connections to SQL Server than there are *active* connections, as idle connections don’t consume a worker thread.
  • A mis-configuration of the max worker threads option through sp_configure
    • Look for the max worker threads option set below the automatically-configured number of threads (see this Books Online entry)
  • Mis-configuring the memory settings so there isn’t enough memory for Windows (anecdotal evidence from someone in email)

If you are unable to connect to SQL Server to troubleshoot because of worker thread starvation, try connecting using the Dedicated Admin Connection.

Known occurrences in SQL Server (list number matches call stack list):

  1. Waiting for a worker thread to become available

Abbreviated call stacks (list number matches known occurrences list):

  1. SOS_Scheduler::UpdateWaitTimeStats+30c
    WorkDispatcher::DequeueTask+211
    SOS_Scheduler::ProcessTasks+1e3
    SchedulerManager::WorkerEntryPoint+261
    SystemThread::RunWorker+8f
    SystemThreadDispatcher::ProcessWorker+3c8
    SchedulerManager::ThreadEntryPoint+236
    BaseThreadInitThunk+d
    RtlUserThreadStart+1d