Make your own free website on Tripod.com

Watch your timeouts
Last updated: March 10th '03 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

This month's 2 click survey:
Is .NET important for a database professional?

SQL Server Articles New

Click here to find out the top 15 SQL Server books purchased by this site's visitors! NEW



Featured Book:
Real-World SQL-DMO for SQL Server, by Allan Mitchell and Mark Allison

Click here to read a sample chapter from the book:
SQL Server Security Distilled
 
NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line

DBA’s Quick Guide to Timeouts

by Chris Kempster
The author of: SQL Server 2000 for the Oracle DBA

Click here for more information, sample chapters, or to purchase this e-book.

The application development DBA needs a good understanding of the overarching application architecture and subsequent technologies (COM+, MSMQ, IIS, ASP etc) to more proactively debug and track down database performance problems. A good place to start is common timeout error. This article will provide a brief overview of where to look and how to set the values.

ADO

Within ADO, the developer can set:

  1. connection timeout (default 15 seconds)
    1. if the connection cannot be established within the timeframe specified
  2. command timeout (default 30 seconds)
    1. cancellation of the executing command for the connection if it does not respond within the specified time.


These properties also support a value of zero, representing an indefinite wait.

Here is some example code:

Dim MyConnection as ADODB.Connection

Set MyConnection = New ADODB.Connection

MyConnection.ConnectionTimeout = 30

MyConnection.Open

- and -

Set MyConnection = New ADODB.Connection

<<set strMyConn>>

MyConnection.Open strMyConn

Set myCommand = New ADODB.Command

Set myCommand.ActiveConnection = MyConnection

myCommand.CommandTimeout = 15

Take care with command timeouts are described by Microsoft:

http://support.microsoft.com/default.aspx?scid=KB;en-us;q188858

COM+

Any component service DLL’s partaking in COM+ transactions are exposed to two timeout values:

  1. global transaction timeout (default 60 seconds)
  2. class level transaction timeout (default 60 seconds)

Opening component services, select properties of "My Computer", the screen shots shown below may differ based on the server and of course the MSDTC version being run. The options tab allows us to set this global value for all registered COM+ DLL’s:


The next layer down is not at the individual COM component, but at the class level within each component. This cannot be programmatically altered. Once installed, drill through to the specific COM and select properties for the class:


Again this option is only available for those classes partaking in COM+ transactions. The GUI also allows transaction support properties to be altered, but this can be fully controlled (and should be) via the developer.

OLEDB Provider Pooling Timeouts

This is somewhat off track for the article, but the DBA should know that control over the pooling of unused open sessions can be controlled at the OLEDB provider level. This is applicable from MDAC v2.1 onwards. Examples of this can be found at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;237977

IIS

Within IIS we can set session timeout values:

  1. globally for the website
  2. for each virtual directory for a website
  3. in a virtual directory global.asa
  4. at the individual ASP page

For the website, open IIS, right click for properties and select the home directory tab. From here click the configuration button. This is very similar for each individual virtual directory, the dialog shown is the same:

Where session state is applicable the default value is 60 minutes, and ASP script timeout default is 90 seconds. The values represent:

  1. session timeout
    1. user sessions - the IIS server can create per user session objects and can be used to maintain basic state information for the "session" via user defined session variables. The session object is created on storing or accessing a session object in ASP code, which will fire the session_onstart event
      1. timeout controlled programmatically via
        Session.Timeout = [n-minutes]
      2. User page refresh will reset the timeout period
      3. Get the unique session ID via Session.SessionID
      4. Control locality that affects date displays etc via Session.LCID = [value]
      5. Create a session variable and show contents:
        Session("myData") = "some value here"
        response.write Session("myData")
    2. application sessions – similar to user sessions but are accessible to all user sessions for the virtual directory. They cannot timeout as such but will reset on IIS being re-started. Its values are typically initialised via the global.asa
      1. initialised on first page served by IIS
      2. use: Application("myData") = "some value here"
  2. script timeout
    1. limit for page execution time
    2. default of 90 seconds
    3. also set via the following for a specific ASP page
      1. <%@ LANGUAGE="VBSCRIPT"%>
        <% Server.ScriptTimeout = 1800 %>

Be aware that IIS has a restricted maximum value for session timeout, that being 24hrs (1440 minutes). In most cases, developers will proactively use a single point of reference for application wide variables, session and script timeouts.

SQL Server


At the SQL Server end, we have these:

  1. LOCK_TIMEOUT
    1. Will timeout the executing command after N milliseconds if it is waiting for locks.
    2. Typically called at the top of in stored procedures, along with set nocount
    3. Is not Pandora’s box for solving deadlock issues
    4. Check value via select @@LOCK_TIMEOUT
    5. Over linked servers, test carefully. You may find that the 4-part naming doesn’t not work, worst still setting the value before using OPENQUERY may also not work. If you experience this problem, try this syntax:
      select *
      from openquery([myremoteserver],
      set lock_timeout 1000 select col1 from myremotetable’)
  2. remote login timeout’
    1. linked server login timeout.
    2. OLE-DB providers, default is 20 seconds
    3. exec sp_configure N' remote login timeout (s)', 1000
  3. remote query timeout’
    1. linked server query timeout.
    2. default 600 seconds (ten minutes)
    3. exec sp_configure N'remote query timeout (s)', 1000
  4. query wait’
    1. default 25 times cost estimated (-1), value in seconds
    2. wait time is resources not available and process has to be queued
    3. if used incorrectly, can hide other errors related to deadlocking
    4. will not stop/cancel blocking issues
    5. set at instance level only
    6. don’t use in an attempt to stop a query after N seconds, its resource related only.
    7. exec sp_configure 'query wait', 5
  5. query governor cost limit’
    1. default zero, value is in seconds, upper time for DML to run
    2. execute/runtime, not parse time
    3. is an estimated figure by the optimiser
    4. globally for the instance
      1. sp_configure ‘query governor cost limit’, 1
    5. manually set per connection
      1. SET QUERY_GOVERNOR_COST_LIMIT 1

Remember that deadlocks are not the same as LOCK_TIMEOUT issues. The DBA should have a careful look at the DTS timeout options; select properties at a variety of levels and active-x objects to determine where values can be set.

Sample Error Messages

Here is a table of sample error messages propagated from a basic web based application to the end user. Be careful, as the actual error message can depend on many factors such as the network providers being used and much more.

ASP Script Timeout

(website or the virtual directory, check both carefully when debugging)

COM+ Transaction

Timeout

or

ADO Connection

Timeout

ADO Command Timeout

Runtime error -214721781 (80040e31)

[Microsoft][ODBC SQL Server Driver]Timeout expired

SQL – Query Governor cost limit reached

Server: Msg 8649, Level 17, State 1, Line 1

The query has been cancelled because the estimated cost of this query (7) exceeds the configured threshold of 1. Contact the system administrator.

SQL - Lock Timeout

Server: Msg 1222, Level 16, State 54, Line 2

Lock request time out period exceeded.

SQL – Query Wait

Server: Msg 1204, Level 19, State 1, Line 1

The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

Is Order Important?

The order really depends on the developer and their specific requirements. Even so, the DBA and developer should have a good understanding of the settings being used to better assist in tracking and resolving timeout issues.

In most cases, the developer is inclined to start timeouts from the DBMS and work up (not necessarily for session timeouts though). Doing so allows the developer to better understand errors related to DBMS connectivity and command execution verses higher level problems in the business and interface layer tiers.

Care must be taken with long running batch jobs or complex SQL. It is not unusual for developers to fix class level transaction timeouts in COM+ and associated IIS level timeout values. Unless you have done a lot of testing, command timeouts are difficult to manage with spikes in user activity.

See attached ZIP file for demo code. Many thanks to Raf Cammarano for his assistance with the VB code.