8

I recently took a look at my production SQL Server's cumulative threadpool waits, and it's literally days worth of waits. So I started doing some digging.

I'm on a 32 core, 64 bit server (running on EC2). Max workers is correctly set to 0 in config, and the following DMV reports the correct, expected number:

SELECT max_workers_count FROM sys.dm_os_sys_info

Output: 960

Looking at my total number of workers, I'm rarely seeing any over 300-400:

SELECT count(*) FROM sys.dm_os_workers

Output: 372

And yet, if I run the following DMV, I always have threads listed:

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

Waiting Tasks -- Threadpool

SELECT dow.state , dow.is_preemptive , dow.is_sick , dow.is_in_polling_io_completion_routine , [Num Workers] = COUNT(1) 
FROM sys.dm_os_workers dow 
GROUP BY dow.state , dow.is_preemptive , dow.is_sick , dow.is_in_polling_io_completion_routine;

enter image description here

The waits in milliseconds are usually pretty short, 20-200 ms generally, and they disappear quickly as well, but they are adding to the overall cumulative threadpool figure. They also never have blocking sessions.

I am stumped as to why anything is encountering a threadpool wait when I have so many available workers. Shouldn't the hundreds of available workers be handling these requests instantly without any threads going to threadpool?

I would appreciate any input or direction here.

SQL Server Version: SQL Server 2016 (SP2-CU8) (KB4505830) - 13.0.5426.0 (X64) Enterprise Edition: (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Cost Threshold for Parallelism is set to 1400.
Hyperthreading is not enabled.
Maximum Degree of Parallelism is set to 8.

3
  • What processes are producing the threadpool waits? Could you try running the query listed here? What is the output? Dec 17, 2019 at 11:58
  • @RandiVertongen The only result coming back from that query is a TRACEWRITE wait_type, coming from SQL Sentry. It doesn't show threadpool waits, even though the other queries do. I suspect because they're coming in and being dispatched so quickly, that query is not picking them up.
    – Mike P.
    Dec 17, 2019 at 23:49
  • I'm having the exact same behavior... all my instances have threadpool waits (in average, less then 200 ms per wait). I have small instance (2 vCpu) and maxDop is set to 50. My machine are mostly idle, when I look at the thread in use, the number is always low... I'm thinking it may have something to do with the VM configuration... are you running on VMWare as well ? Sep 1, 2020 at 14:09

2 Answers 2

2

If you have the max degree of parallelism set to zero, which is the default, then queries that might benefit from parallelism will consume 32 threads. With the number of workers set to 960, again the default for your setup, you could only run 30 simultaneous parallel queries.

I'd recommend setting max degree of parallelism to something sane, like 8.

0
1

SQL Server's "thread pool" is not always fully backed by real threads in Windows. You might think of it as a "logical thread pool." The thread objects themselves require memory, and are eventually released back to Windows if they haven't been used in a while.

What you're seeing is a "thread ramp up" period - requests that are waiting on a worker thread to actually allocate a thread through Windows APIs will incur fairly short THREADPOOL waits.

When you see short THREADPOOL waits like that, it's normally because you've experienced a sudden burst or increase in your workload, possible after a period of idleness (or just lower activity).

This is normal, expected behavior - but it's a little confusing if you're not familiar with it!

I have written about this on my blog, including a demo if you want to "prove" the behavior to yourself:

Unusual THREADPOOL Waits

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.