We are running SS2005 behind our website. We are still using classic ASP. We are experiencing timeouts when trying to open a connection using ado. This started happening around Nov/Dec of 2012. There is no pattern on what days it happens. However the times are always 8:55a, 5:05p and 8:05p. Note these are the time when it happens, but one day it may be 8:55a and the next day 5:05p. Then maybe the next day it happens at all three times. We have a lot going on at the top of the hour, but it's every hour not just these times. Also I do not see any jobs that run only at theses time. It's so frustrating because the times are so specific.
What is odd is we've seen timeouts on queries being run from ASP, but I've never seen consistent timeouts when trying to open a connection from ASP until this started happening. To me that is an important factor. To me it seems a network issue. I want to note too that I'm not seeing any issues with the jobs, only the connections from ASP to our db server. However this could be because our jobs run on a different NIC than our IIS server, for the most part. This again leads me to a network issue.
I have performance monitoring setup some common network, system and sql server counters, but I do not see anything obvious. However I'm not an expert so I may have missed something.
I've also recorded the output of sp_who2 every 10 seconds during these periods. I haven't found the issue, but I do see the connections pile up after the issue has happened. Probably a jump from 40-50 to 80-90 open connections. So something is bogging down or backing up, but I don't see anything even running. Everything is "awaiting command" except for my sp_who2 call. Also there is no blocking going on. Even the lead up to the bog down I'm not seeing any process running a command in sp_who2. Basically what I'm seeing is around 40-50 processes "awaiting command" every 10 seconds and then all of the sudden I'm up to 80-90 processes "awaiting command". One interesting thing I saw yesterday was that there was a 30 second gap, instead of 10, between two sp_who2 calls. So whatever was happening delayed the call. This isn't consistent though.
Based on the performance monitoring and sp_who2 data I don't see anything obvious with sql server. I'm leaning towards something bogging down the network. I might try to pull out the network counters from my performance monitoring and make a separate one so it's easier to view.
Can anyone add some input or point in the right direction as far as what I should be monitoring and what I should be looking for?