Over the past two years, we have seen 5 or 6 incidents where we started to get login timeouts and performance started to degrade. One thing that we know for sure during those times was that we had an abnormally high number of processes running on the instance at that time.
After reading thru many articles, BOL, and blogs, it seems that we reached the max worker threads during these incidents.
I am wondering if you can help answer a couple questions. Which DMV (select count(*)) would give us an indication of when we are reaching the max worker threads: sys.dm_os_threads or sys.dm_os_workers or other?
Do you have a script that joins information about the workers and processes, so that we can see application and login associated with worker thread?
If we look at the different statuses in processes (sleeping, runnable, background, etc), which of these would have a worker thread tied to them?