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.

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.

Here is some example code:

Take care with command timeouts are described by Microsoft:

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.

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:

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.

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.

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.

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.