July 19, 2015 at 5:34 pm
I have a MSSQL database server 2008R2 X64 that currently has around 140 databases associated with it with a very mixed workload .The server has 2 @ 12 core processors with 192 GB of ram .
Recently I received an alert from SCOM with a thread count alert saying it is around 1200 threads. Using the DMV max_workers_count it returns a count of 960.
When I run this query
Select is_preemptive,state,last_wait_type,count(*) as NumWorkers from sys.dm_os_workers Group by state, last_wait_type,is_preemptive order by count(*) desc
To get number of workers I see the majority are state suspended with a wait type miscellaneous and number of workers in suspended state around 570 SCOM data collection over a 24 hr period reports min 670 threads max 1200 threads with an average of 831 thread count.
Now that I have been alerted to a high thread count how do I know if there is an issue or if this is normal and acceptable behaviour .Because this has been logged I also need to prove or disprove this is or isn’t an issue .
Any advice or assistance would be gratefully accepted. If there is any more information needed I can provide this
July 19, 2015 at 9:06 pm
High thread counts aren't an issues usually and just reflects the load on the server , e.g parrallelism/ large number of users etc but when you have a large number of thread in suspended state it usually indicates an issue with another part of the system. Basically a thread is suspended when it reaches the cpu ready for work but ends up not having all the data pages or some other resources needed to complete the job , at this point the CPU suspends the thread and kicks it out of the queue until its ready. Check the number of sessions and requests per second. Also monitor disk and memory utilization to see if any alarms go off.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply