Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Posts
    56

    Unanswered: Stored Procedure timeout mystery ...

    Guys,

    In simple terms, our system is as such: We have a website. As someone clicks a button on the website, a stored procedure is executed against our database.

    Every single day, between 12:15AM and 12:45AM we have a few stored procedures timing out, with the following message, for example:

    2007-04-10 00:37:03,268 [3632] ERROR Service - caught exception Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)

    I checked and saw that although there are jobs running at that time, all of these jobs are running periodically (e.g. every 30 minutes) and would cause timeouts at other times as well, if they were to blame. Other jobs are running at far away times and checking their history I know that their duraion in no way intersects the time-out times.

    I also ran profiler during peak hours and know that no stored procedure of ours has a duration anywhere near 30 seconds (which is the currently set timeout period, although all of our sps run within milliseconds).

    I am really puzzled as to what exactly is causing these timeouts. Would anyone suggest any approach to identify the problem. For example, I thought about running profiler (server side tracing) between 12AM and 1AM, but am not sure which counters are best to capture. Any suggestion on this?


    Thanks a lot!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    respondents FYI:
    http://www.sql-server-performance.co...TOPIC_ID=20669
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81941
    http://www.sqlservercentral.com/foru...ssageid=357509

    sql_er - if you cross post in multiple boards please mention in the outset of your post so that people don't go up blind alleys that have been covered elsewhere. Is this on any other board?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2003
    Location
    United States
    Posts
    65
    Provided Answers: 1
    What is the current timeout set in the web application ?
    >>
    >> Learn PHP/MySQL for free https://www.youtube.com/watch?v=mpQts3ezPVg
    >> Free Web Hosting with PHP, MySQL, Website Builder : http://www.000webhost.com/862861.html

  4. #4
    Join Date
    Jan 2007
    Posts
    56
    Pootle: I try to get information from as many places as possible, as different people, in different places, have experiences with different things. I always try to share the knowledge everywhere, once a good solution is suggested. I believe it is beneficial for everyone. A blind approach would be to confine to a single place and not look around.

    gvphubli: Timeout is set to 30 seconds in the applications, but we never expect any stored procedure to exceed 1 second, although I did capture up to 3 seconds in some cases with the Profiler

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by sql_er
    Pootle: I try to get information from as many places as possible, as different people, in different places, have experiences with different things. I always try to share the knowledge everywhere, once a good solution is suggested. I believe it is beneficial for everyone. A blind approach would be to confine to a single place and not look around.
    No problem with that - just let the people that are giving up their time to help you that you have other irons in the fire. I am not thinking about your benefit here - just our contributors.

    Just as an FYI - as a contributor on a couple of forums I usually skip any posts by cross posters. My experience is that they spread themselves very thinly, are unable to fully participate in threads and tend to abandon them once they get some joy on another board. Others might well regard things differently. I am mentioning this in case you should ever find that a machine gun approach seems to be eliciting little in the way of responses and you start thinking about changing your posting strategy.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •