6

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)

  • What is Cost Threshold for Parallelism set to? Is it still the default of 5? SELECT * FROM sys.configurations WHERE name LIKE 'cost%' – John Eisbrener Dec 16 '19 at 15:07
  • @JohnEisbrener 1400. – Mike P. Dec 16 '19 at 17:35
  • Is hyperthreading enabled on this server? If so, have you tried disabling it and testing again? – John Eisbrener Dec 16 '19 at 17:53
  • What processes are producing the threadpool waits? Could you try running the query listed here? What is the output? – Randi Vertongen Dec 17 '19 at 11:58
  • @JohnEisbrener Hyperthreading is not enabled. – Mike P. Dec 17 '19 at 21:52
3

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.

| improve this answer | |
  • 2
    Thanks for the comment. MAXDOP is already correctly set to 8, with a threshold that makes sense for when queries should go parallel (for this server, about 1400). – Mike P. Dec 11 '19 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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