1

Product : SQL Server 2008 (SP4)

CPU : 64

64-Bit system

Worker thread configuration = 0

SCOM Alerts : SQL DB 2008 Engine Thread Count is too high


After receiving this alert from SCOM i have queried [sys.dm_os_sys_info] and
[sys.dm_os_threads] to check the Total number of threads \ used \ available and found them as follow :

Total --- Used --- Free --- Used Percentage

1472 --- 1260 --- 212 ----- 85 %

this server is used for online transactions processing. Is it normal that the Used threads are always between 1100 and 1300 throughout the day ? which obviously cause this alert to be sent when too many queries are executing on the server ?

Moreover, Is this related to the configuration of MAX Degree of Parallelism (Which is set to 0) that causes queries exceed threshold use parallel threads to finish ?

Thanks

closed as unclear what you're asking by Max Vernon, Tom V, Philᵀᴹ, RolandoMySQLDBA, Andriy M Feb 10 '16 at 17:15

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • How many simultaneous clients do you expect to have? – Max Vernon Feb 9 '16 at 12:46
  • Does your server have 64 cores? – Max Vernon Feb 9 '16 at 14:57
0

You should set MAXDOP and "Cost Threshold for Parallelism" according to best practices.

MAXDOP should generally be set to the number of cores per NUMA node. See my question here for more details.

Cost Threshold for Parallelism should likely be set to at least 50.

  • Thanks for your reply. Does this mean that when MAXDOP = 0 and cost threshold for paraellelism = 5 queries will use multiple threads and that why i see worker threads between 1000 - 1200 all the time ? – Osama Waly Feb 9 '16 at 14:26
  • That might be causing the symptoms. How many simultaneous clients/queries are running on your server? A lot? – Max Vernon Feb 9 '16 at 14:57
  • well, it depends on the time of the day as this instance is mainly for online transactions. are you asking about a specific value on a DMV ? – Osama Waly Feb 9 '16 at 15:15
  • 1
    Nope its not attached to a website at all. All is payments transactions. anyway i have checked the wait_info and found CXPACKET OVER 55% which makes me going forward re configuring the MAXDOP and Cost Threshold. – Osama Waly Feb 9 '16 at 15:33
  • 1
    Thanks Max, Reconfiguring MAXDOP and Cost Threshold solved this issue and thread count is normaly now – Osama Waly Feb 11 '16 at 8:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.