Sql-server – Thread Count is too high-what can I do in short term

cpusql server

Before I posted this question read following articles.

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.

Best Answer

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.

  1. 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?
  2. 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?
Related Question