SQL Server Performance

SQL Server 2005 - Connection timeouts at specific times

Discussion in 'ALL SQL SERVER QUESTIONS' started by ggeller, Mar 27, 2013.

  1. ggeller New Member

    We are running SS2005 behind our website. We are still using classic ASP. We are experiencing timeouts when trying to open a connection. This started happening around Nov/Dec of 2012. I haven't seen a pattern on what days, but it's not every day. However the times are always 9a, 5p and 8p. 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.

    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?

    EDIT: I have confirmed there is no pattern as far as what days this happens. It has happened on each day of the week at some point.
  2. ggeller New Member

    It turns out it was a job after all. I was able to run sp_who2 at the right time and that's how I saw a job was running.

Share This Page