Troubleshooting Timeout SqlExceptions

Question

I have some curious behavior that I'm having trouble figuring out why is occurring. I'm seeing intermittent timeout exceptions. I'm pretty sure it's related to volume because it's not reproducible in our development environment. As a bandaid solution, I tried upping the sql command timeout to sixty seconds, but as I've found, this doesn't seem to help. Here's the strange part, when I check my logs on the process that is failing, here are the start and end times:

• 09/16/2008 16:21:49
• 09/16/2008 16:22:19

So how could it be that it's timing out in thirty seconds when I've set the command timeout to sixty??

Just for reference, here's the exception being thrown:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at SetClear.DataAccess.SqlHelper.ExecuteReader(CommandType commandType, String commandText, SqlParameter[] commandArgs)

Answer 1

Try changing the SqlConnection's timeout property, rather than that of the command

Answer 2

Because the timeout is happening on the connection, not the command. You need to set the connection.TimeOut property

Answer 3

This may sound stupid, but just hear me out. Check all the indexes and primary keys involved in your query. Do they exist? Are they fragmented? I've had a problem where, so some reason, running the script outright worked just find, but then when I did it through the application, it was slow as dirt. The reader's basically act like cursors, so indexing is extremely important.

It might not be this, but it's always the first thing that I check.

Answer 4

I had this problem once, and I tracked it to some really inefficient SQL code in one of my database's views. Someone had put a complex condition with a subquery into the ON clause for a table join, instead of into the WHERE clause where it belonged. Once I corrected this error, the problem went away.

Answer 5

SQL commands time out because the query you're using takes longer than that to execute. Execute it in Query Analyzer or Management Studio, with a representative amount of data in the database, and look at the execution plan to find out what's slow.

If something is taking a large percentage of the time and is described as a 'table scan' or 'clustered index scan', look at whether you can create an index that would turn that operation into a key lookup (an index seek or clustered index seek).