SQL Server Performance

Maximum time allowed for a stored Proc

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by AMOURGH, Feb 25, 2009.

  1. AMOURGH Member

    Hello Everybody,
    I have a question:what's the maximum time allowed by users for a stored Proc.
    I have a stored proc that is doing some caluculations(for each businessunit,services consumption evolution for a year==>calculate the consumption of each month ).
    I wonder if it is allowed that it i needs 1 min 30 to execute.
    Thank u
    Rachid,
  2. Adriaan New Member

    I don't think there's a fixed limit from the SQL Server side. There is the SET QUERY_GOVERNOR_COST_LIMIT option, but this will be restricted to the current connection.
    From the client application, there is usually a time-out setting for the connection. The client application will not wait longer than the given number of (milli)seconds for any results to be returned. I presume that the server will still be processing until it finishes, just that the end results will never reach the client application.
  3. FrankKalis Moderator

    I think most Microsoft APIs use 30 seconds as default timeout.
    We had this recently and found, that when the client relied on the garbage collector to kick in and clean up after a timeout instead of explicitly closing the connection, the timeout occured but the client didn't send anything to abort the server processing and the server happily kept on processing and processing. We went through the error handling articles on http://www.sommarskog.se and applied some code Erland provided there to make the client react specifically to a timeour error. Since then we also use XACT_ABORT in every procedure, btw.
  4. FrankKalis Moderator

    [quote user="Adriaan"]
    From the client application, there is usually a time-out setting for the connection. The client application will not wait longer than the given number of (milli)seconds for any results to be returned. I presume that the server will still be processing until it finishes, just that the end results will never reach the client application.
    [/quote]
    The connection timeout setting just specifies, how long a client should wait for a connection to be established. I think, you mean the command timeout here.
  5. Adriaan New Member

    Frank - you must be right, I guess depends on which programming interface you're using.
    Good old DAO simply offers "QueryTimeout", which doesn't seem to care if it's a connection timeout or a command timeout.
  6. FrankKalis Moderator

    Aah...
    I don't know if this is true or not in DAO. I was thinking about ADO and I guess it is also in ADO.NET.

Share This Page