Threadpool - A deadly poison wait for SQL Server (The What, When and How)

Introduction 

Threadpool is a poison wait. Yes, I mean it. Its poison for SQL Server, its poison for the Business and of course, the end-users! The most devastating thing about threadpool is you hardly recognize it because it comes in disguise, meaning you see no memory or cpu pressure in the system, yet you cannot run any query, it seems like your SQL Server is frozen solid. That scary, isn't it?

Today we will try to dig into three fundamental things about threadpool -  

• What is threadpool
• When we can understand its occurring
• How can we tackle it  

Threadpool

Let's understand the flow of a web application first. I will try to simplify things as much possible.

• User logs into your system
• Your app server provides a connection to communicate with the server
• App server connection then connects with the Database Server
• The database connection is then used for Database request
• SQL Server decides on how many threads it's going to use based on mainly
•  Your query cost
• SQL Server maxdop Settings
• Cost threshold for parallelism
• When all threads associated with your request are done fetching data, the request is returned to your app connection.

Max Worker Thread and Threadpool 

Lets first see how to calculate the current number of threads available for your SQL server. The following query gives you the number of threads available - 

SELECT max_workers_count FROM sys.dm_os_sys_info

Which outputs like this - 

 

So when you run out of this available number of threads, new incoming connections cant have a thread to start with.

Symptoms 

• Login Failures
• Queries not being able to run
• No major CPU, Memory pressure is observed.

How to track Theadpool

You can track threadpool with SQL Servers DMV named system.os_dm_waiting_tasks. This DMV keeps track of all the current waiting tasks in your SQL Server. The following Query will output threadpool wait if you have any.

SELECT * 
FROM sys.dm_os_waiting_tasks
WHERE wait_type = 'THREADPOOL'
GO

Increasing Worker Thread 

Since we are facing thread shortage, can we increase the maximum number of threads? The answer is yes we can. Can increasing the number of threads solve our problem?  The answer is a big NO. The optimal value of this particular setting is the default value. Yes, you read it right. Leave these settings as it is.

What if you increase the max thread?

If you increase the max worker threads when your system is running out of threads, it's quite possible that your system may not be able to handle the increase and you may notice more and more waiting threads. In short, the situation will just get worse.

Resolutions & Recommendations  

• Maxdop and cost threshold Setup

• Maxdop stands for Max degree of parallelism. It controls how many maximum number of processors or cores your SQL Server can take if it decides to execute a query in parallel. Maxdop(0) gives SQL Server permission to use as many processors it needs to perform an operation. You may find queries running fast in the beginning when you have Maxdop(0) since lots of processors are working togerther for your query. But once you use more processors, they are going to use more threads and after sometime, you end up facing threadpool. I have discussed here How you can set proper maxdop and cost threshhold for your SQL Server
• Cost Threshold for parallelism is the threshold cost which decides when SQL Server will  go parallel. if your cost threshold is 5, SQL server is going to perform parallel execution for the queries which have a cost more than 5.  You have varieties of queries running in your system. Some of them fetch filter data for your report, some of them fetch the report data itself. You need to understand these two cannot be treated in the same way. If you put the cost threshold value to somewhat 5, and your filter query has a cost of 6 and the report query has a cost of 40. Both of them are going to go parallel and take up lots of threads before you even notice.

• Optimize IO intensive queries

• You need to track down your most expensive queries and tune them one by one so that they can free up the threads fast. This will help other waiting queries to get a thread.

• Optimize for Ad Hoc Workloads

• Find your Ad Hoc workloads and optimize them as much as possible

• Avoid Building large spatial indexes

• Large spatial indexes can occupy lots of processing power for your SQL Server and use more threads

• Using temp tables/variables to load or update rowsets

• Whenever you need to get or update rows, you can take those in temp variables first before doing further processing, like JOIN or UNION. Whenever you need to reuse any datasets, never fetch it multiple times. If you need to use the temp table once, consider using CTE instead of temp table.

• Servers with more than 8 cores per NUMA node may require trace flag 8048

• It converts NUMA partitioned memory objects to CPU partitioned memory objects. You can find out more on it How to mitigate high log write waits (MSDN)

Conclusion

In this article, we have discussed what is threadpool, why threadpool is very deadly, how can we understand that we are facing threadpool, What can we do about it and How can we avoid Threadpool.

See Also

• How intelligent Query Processing in SQL 2019 can Reduce Memory Grant
  
• Is Union ALL better than OR ?
  
• Why you should NOT write SELECT * ever again