How to troubleshoot ODBC timeout errors?
Last updated: May 7th '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

See also:   Database coding conventions SQL Server interview questions Evaluation of federated database servers SQL Server FAQ on programming, administration, replication and tools SQL Server security best practices SQL Server administration best practices

Related books:

How to troubleshoot ODBC timeout errors experienced by applications accessing SQL Server?

There are a lot of reasons why one ends up with a timeout error. This article discusses the most common reasons that result in timeouts. For more specific information on handling timeouts, along with code samples, read the article: DBA's Quick Guide to Timeouts

By default, ADO connections time out after 30 seconds. So, your query or stored procedure is obviously taking more than 30 seconds to complete. You can verify this by running your query/stored procedure in Query Analyzer and noting the execution time. You can also use Profiler to see the execution time of queries (Start time, End time and Duration columns in Profiler).

Once you confirm that the query indeed is taking more than 30 seconds, find out why it is taking more than 30 seconds and see if you can tune it to make it quicker. The most obvious step is to look at the Execution plan in Query Analyzer. Any table scans are bad. Make sure you have right indexes on your tables, and that the query is written in an efficient way and is using those indexes (Index seeks are preferred). If you are not sure, how to tune indexes, try the Index Tuning Wizard. Btw, if you are using cursors, see if the query can be rewritten without using a cursor. Cursors make things slow.

Consider that indexing is fine, but still the query is slow. Few things to look at: Is your query doing clustered index scan, and the index is fragmented? If so, you need to rebuild the index using DBCC DBREINDEX or DBCC INDEXDEFRAG (in SQL Server 2000 only). I rebuild indexes on some of my tables evrey weekend using a scheduled job.

Assume that there is no fragmentation in the index. Next thing to verify would be to see if there is any blocking going on. Use the system stored procedures sp_who or sp_who2 (undocumented, but provides more info, compared to sp_who) to verify blocking. If you do find that there is some blocking, you need to troubleshoot the blocking first. The following topics from SQL Server Books Online will help you troubelshoot blocking:

- Understanding and Avoiding Blocking

- Troubleshooting Locking

Another approach for troubleshooting timeouts is to use Performance Monitor to identify the bottlenecks. A bottleneck could be a slow disk subsystem, or slow/insufficient processing power (CPU), not enough RAM, or network. In this case, a hardware upgrade should help.

If none of the above help, just increase the timeout value (The CommandTimeout property of Connection and/or Command object) in your ADO application!