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.