How to reduce THREADPOOL wait type

The THREADPOOL wait type is recorded when a task is waiting to get assigned to a worker thread. You would see this wait type appear in sys.dm_os_waiting_tasks.

These are some of the causes I’ve seen causing the THREADPOOL wait type during SQL performance troubleshooting

1) High amount of connections require queries to run queries simultaneously.

2) SQL Server reaches the max worker threads.

3) High levels of long blocking – may force worker thread requests

4) Extended stored procedures

Some other symptoms you may see:

Thread pool starvation i.e no more worker threads to process requests

By default max worker threads it is set at a 0 – therefore SQL Server will calculate the amount of worker threads. You can check by using:

select max_workers_count from sys.dm_os_sys_info

How to reduce THREADPOOL wait type

Review parallelism. In sql server performance troubleshooting – this is a common reason causing the Threadpool waits. During parallelism multiple worker threads are required. CXPACKET is commonly associated with the THREADPOOL wait type in this content.

Review level of lock waits – Increase in user connections particularly if the connections remain active

Index tuning – therefore more optimal response times, releasing worker threads as quickly as possible

Should you change the Maximum Worker Threads number?

1)            One of the consequences would be an increase in context-switching. This could have performance degradation issues

2)            Every time there is a worker thread request – a little extra memory is required

3)            It is not a good idea- focus on identifying the root cause. Exhaust all analysis and possible reasons for this wait type

Read More

Top 5 SQL Server DMV for Index Analysis (SQL Server DBA)

How to troubleshoot a slow running query in SQL Server

SQL Server Performance Checklist