0

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.

3

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?

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.