Hi Kev,
" Can anyone explain the discrepancy between max_workers_count and the calculation as I presented it here?"
You can set the max number of threads manually (basically not recommended) with
sp_configure N'max worker threads', num_of_threads;
RECONFIGURE WITH OVERRIDE;
You can check the actual set value by querying the system dmv [sys].[configurations]!
"Why is there such a large amount of THREADPOOL waits when clearly we aren't exhausting the available workers?"
The reason is “MAXDOP is always specified per operator in the execution plan and not per execution planâ€.
If [THREADPOOL] waits occure it might be the reason of complex execution plans with lots of parallel operators!
Hi Uwe,
OK.....now you have touched on a subjct I clearly didn't understand enough. Some of our execution plans are very complex and deeply nested so this would explain the requirement for what would appear to be an excessive number of threads for a given query.
Max Workers Threads we have left alone with the default of 0. This won't be changed.
What I am going to suggest based on your explanation is a test of the code with a reduced MAXDOP to see how it reacts. I will post the results here.
Many thanks!
Regards,
Kev