SqlExceptions Bug Fixes

Question

I have some kind of curious behavior in which it is difficult for me to understand why this is happening. I see intermittent timeouts. I am sure that this is related to volume because it is not reproducible in our development environment. As a solution for bandages, I tried to increase the sql command timeout to sixty seconds, but as I found it does not seem to help. Here's the weird part, when I check my logs for a process that fails, here is the beginning and the end:

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

So, how could it be that it was timing in thirty seconds when I set the team timeout to sixty?

For reference only, here is the exception:

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

- SQL, , . Query Analyzer Management Studio , , .

- " " " ", , , ( ).

Answer 2

, . , . ? ? , , - script , , , , . , .

, , , .

Answer 3

SqlConnection,

Answer 4

- , . connection.TimeOut

Answer 5

I had this problem once, and I tracked it to some really inefficient SQL code in one of my database views. Someone put a complex condition with a subquery in the ON clause to join the table, and not in the WHERE clause where it belonged. As soon as I fixed this error, the problem disappeared.