Watch your timeouts
My technical skills
My picture album
My code library
Search my site
Sign my guestbook
SQL Server Articles New
DBA’s Quick Guide to Timeoutsby 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.
Within ADO, the developer can set:
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
- and -
Set MyConnection = New ADODB.Connection
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
Any component service DLL’s partaking in COM+ transactions are exposed to two timeout values:
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
Within IIS we can set session timeout values:
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:
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.
At the SQL Server end, we have these:
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.
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.