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

Osama Waly
  • 151
  • 1
  • 5
  • 15

1 Answers1

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.

Community
  • 1
Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323