My picture album
My code library
Search my site
SQL Server Articles New
NEW!!! Subscribe to my
|Want to keep in touch with the
latest in SQL Server world? Email firstname.lastname@example.org with
'subscribe' in the subject line
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