Procedure timeout when executing from the ASP.NET application

Discussion in 'ASP .Net' started by Peter Afonin, Sep 1, 2010.

  1. Peter Afonin

    Peter Afonin Guest

    Hello,

    I'm working with ASP.NET 3.5 and SQL Server 2008.

    I have a very weird problem. When I execute the procedure from my ASP.NET
    application (either from my PC or the Web server), it's timing out. At the
    same time when I execute the same procedure with the same parameters from
    the SQL Server Management Studio - it executes instantly, so aparently there
    is nothing wrong with it.

    This applies only to the few procedures, with most of them I don't have any
    issues.

    To add to this mistery, this problem is intermittent, i.e. occasionally
    everything works OK.

    It seems that something is happening somewhere in the middle tier between my
    application and the SQL Server, but I have no idea what could it be and how
    to find the problem.

    I would appreciate any comments on this.

    Thank you,

    Peter
     
    Peter Afonin, Sep 1, 2010
    #1
    1. Advertising

  2. Peter Afonin

    Mr. Arnold Guest

    On 9/1/2010 12:22 PM, Peter Afonin wrote:
    > Hello,
    >
    > I'm working with ASP.NET 3.5 and SQL Server 2008.
    >
    > I have a very weird problem. When I execute the procedure from my ASP.NET
    > application (either from my PC or the Web server), it's timing out. At the
    > same time when I execute the same procedure with the same parameters from
    > the SQL Server Management Studio - it executes instantly, so aparently there
    > is nothing wrong with it.
    >
    > This applies only to the few procedures, with most of them I don't have any
    > issues.
    >
    > To add to this mistery, this problem is intermittent, i.e. occasionally
    > everything works OK.
    >
    > It seems that something is happening somewhere in the middle tier between my
    > application and the SQL Server, but I have no idea what could it be and how
    > to find the problem.
    >
    > I would appreciate any comments on this.
    >
    > Thank you,
    >
    > Peter
    >
    >
    >


    Is this something you're playing with or is this something at the
    enterprise level?

    One issue you might be having if lots of users are using the application
    or this application is being used with other applications that use SQL
    Server heavily is that connections are being left opened by an
    application, which will cause timeout issues as connection limits to SQL
    Server have been reached.

    Any application that wants a new connection under this condition is
    going to wait for a connection to be released. And if the wait is too
    long, past the set 'Connection Timeout', then the application is going
    to throw a timeout condition.

    Another issue might be that ADO.NET is not using SQL Server connection
    pooling, which is the default setting.

    You can go to the SQL Server Manager application, 'Create a new Query'
    and run the sp_who or sp_who1 system sprocs, which will give you
    information as to how many concurrent connections are opened, and what
    userid has the connection open, which should be done at the time of
    connection timeout issues with your application. If you see a lot of
    userid(s) with connections open, then it may be a sign the connection
    pooling is not be used, pooling is not being used right or connections
    are not being closed properly by an application.

    Lastly, you just might have to set the Connection Timeout to a higher
    setting too with your application to avoid the timeout issues on the
    connection string.
     
    Mr. Arnold, Sep 1, 2010
    #2
    1. Advertising

  3. Peter Afonin

    Peter Afonin Guest

    Thank you.

    The application is in testing stage, so the number of connections is not an
    issue. All procedures in the production environment don't give us any
    problems.

    I'd checked all other settings - they are OK.The connection timeout is set
    to maximum. Usually the procedure finally works, it just takes about 2-3
    minutes to execute, which is not acceptable.

    Peter

    "Mr. Arnold" <> wrote in message
    news:...
    > On 9/1/2010 12:22 PM, Peter Afonin wrote:
    >> Hello,
    >>
    >> I'm working with ASP.NET 3.5 and SQL Server 2008.
    >>
    >> I have a very weird problem. When I execute the procedure from my ASP.NET
    >> application (either from my PC or the Web server), it's timing out. At
    >> the
    >> same time when I execute the same procedure with the same parameters from
    >> the SQL Server Management Studio - it executes instantly, so aparently
    >> there
    >> is nothing wrong with it.
    >>
    >> This applies only to the few procedures, with most of them I don't have
    >> any
    >> issues.
    >>
    >> To add to this mistery, this problem is intermittent, i.e. occasionally
    >> everything works OK.
    >>
    >> It seems that something is happening somewhere in the middle tier between
    >> my
    >> application and the SQL Server, but I have no idea what could it be and
    >> how
    >> to find the problem.
    >>
    >> I would appreciate any comments on this.
    >>
    >> Thank you,
    >>
    >> Peter
    >>
    >>
    >>

    >
    > Is this something you're playing with or is this something at the
    > enterprise level?
    >
    > One issue you might be having if lots of users are using the application
    > or this application is being used with other applications that use SQL
    > Server heavily is that connections are being left opened by an
    > application, which will cause timeout issues as connection limits to SQL
    > Server have been reached.
    >
    > Any application that wants a new connection under this condition is going
    > to wait for a connection to be released. And if the wait is too long, past
    > the set 'Connection Timeout', then the application is going to throw a
    > timeout condition.
    >
    > Another issue might be that ADO.NET is not using SQL Server connection
    > pooling, which is the default setting.
    >
    > You can go to the SQL Server Manager application, 'Create a new Query' and
    > run the sp_who or sp_who1 system sprocs, which will give you information
    > as to how many concurrent connections are opened, and what userid has the
    > connection open, which should be done at the time of connection timeout
    > issues with your application. If you see a lot of userid(s) with
    > connections open, then it may be a sign the connection pooling is not be
    > used, pooling is not being used right or connections are not being closed
    > properly by an application.
    >
    > Lastly, you just might have to set the Connection Timeout to a higher
    > setting too with your application to avoid the timeout issues on the
    > connection string.
    >
    >
    >
    >
    >
     
    Peter Afonin, Sep 2, 2010
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Peri
    Replies:
    1
    Views:
    404
    Patrick Olurotimi Ige
    Jun 13, 2005
  2. Mike P
    Replies:
    0
    Views:
    3,310
    Mike P
    Jun 19, 2006
  3. AlexWare
    Replies:
    2
    Views:
    763
    Paul Uiterlinden
    Oct 23, 2009
  4. Gary Homewood

    Handle stored procedure timeout from ASP

    Gary Homewood, May 3, 2005, in forum: ASP General
    Replies:
    1
    Views:
    148
    Gary Homewood
    May 4, 2005
  5. Mark Probert

    Timeout::timeout and Socket timeout

    Mark Probert, Oct 6, 2004, in forum: Ruby
    Replies:
    1
    Views:
    1,293
    Brian Candler
    Oct 6, 2004
Loading...

Share This Page