Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jmbone
Starting Member
4 Posts |
 Posted - 2009-03-18 : 11:40:05
|
| 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?Jana |
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-03-18 : 17:01:48
|
| "we reached the max worker threads during these incidents" - What made you for sure about running out of MAX WORKER THREADS? Any specific error in error log/Event log. It looks you're running SQL 2005 where default is 1024 Max threads. If you have upgraded from SQL Server 2000 to SQl Server 2005 (in place upgrade) and forget to change Max worker thread (Max is 255 in SQL Server 2000) setting as a part of post upgrade configurations than do it now. In performance monitor select PROCESS - Threads and chose instace SQLServer. This will answer your no. of threads question. |
 |
|
|
jmbone
Starting Member
4 Posts |
Posted - 2009-03-18 : 19:42:31
|
| No, I don't know for sure that we reached max worker threads:"it seems that we reached the max worker threads during these incidents."Which is why I am wanting to monitor to determine where our count actually is.....Jana |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-03-18 : 22:02:25
|
| You can use sp_configure to find out what is configured to run currently. |
|
|
|
|
|
|
How to monitor for max worker threads?