Thread Count is too high-what can I do in short term? Before I posted this question read following articles.


Max. Worker Threads and when you should change it by Sakthivel Chidambaram
Are my actual worker threads exceeding the sp_configure 'max worker threads' value? by Tom Stringer
How do you measure CPU pressure? by Thomasda
SQLOS’s DMVs Continue by Slavao


I am familiar with investigating high thread count issue by looking at following items (not comprehensive):


Maxdop setting
Cost threshold of parallelism setting
Cpu pressure
Worker queue length
Execution plan


This investigation can take hours before I decide to make a change. What can I do in short term so the requests are not waiting for worker threads to be assigned?

We use SCOM to monitor many aspects of SQL Server and this is one of them. I understand this is more of a informational message but seeing is consistently from same server gives us opportunity to look at the things that I listed above. What I am trying to find out from the community, if they take certain action (can be temporary, till full evaluation is done) if work_queue_count is constantly greater than zero once alert is triggered by SCOM. There are times it is easy to detect a single query that is consuming lot of threads but not always.
Default formula for [max workers] (at or below 64 logical CPUs): 16*(logical CPUs - 4) + 512

Here are two potential justifications if you want to raise [max workers]. If CPU is not saturated when DOP downgrades occur due to reserved worker count, negative consequences are limited. YMMV.


General: in sql server 2019 (maybe before? i’ve Not checked) if above 64 logical cpu/user schedulers, the multiplier for default is changed from 16 to 32. for the systems i care about, expected intensity of work per scheduler is the same whether 8 vcpu or 80. So why not always use 32?
specific to positioning re: hyperthreading.
again, for my workloads, expected intensity of work per core is the same whether HT or not. But the VMs have 1 vcpu per pcpu. And some physical servers have HT disabled. In those cases, why not use the “theoretical” logical cpu/scheduler count for the system if HT were enabled?