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

THREADPOOL

(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. For instance, on my laptop with 8 logical processor cores, there are 576 worker threads. You can see how many threads are on your instance from the max_workers_count in 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. If there are not enough available threads, then THREADPOOL waits occur. If there are no available threads, connections to SQL Server will also fail.

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 conenctions to SQL Server than there are *active* connections, as idle connections don’t consume a worker thread.
  • A misconfiguration 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)

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