Multiple Simultaneous SQL Connection Timeouts (Threading)

Question

I am working with a Console app that is using Parallel.foreach for threading purposes. It was developed using .net 4.5 vs 2012 that is retrieving and inserting data into sql server (2008 r2) that is on site. So there should be no network issues because the sql server is local. My application is currently the only one using the database I am getting a ton of timeouts from several threads at the same time. What makes this more confusing is I can call this application over and over but that one time I get the timeout. Maybe this is simple as increasing the timeout but I wonder if there is something else going on

This is the stored procedure I am calling

Select table.Id from Table WITH (NOLOCK)
        inner join table2 c WITH (NOLOCK) on c.Id = table.TableId 
        inner join Table3_xref  x WITH (NOLOCK) on x.TableID = c.id 
        inner join Table4 p WITH (NOLOCK)  on p.id = x.id 
        where 
         value = @Parm1
         and p.Value1 = @Parm2
         and c.Value2 = @Parm3
         and Table.Void = 0

This is the exception

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() 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, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) 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.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

This is what my code looks like

  try
            {
                using (DbConnection con = Database.CreateConnection())
                {
                    IDbCommand cmd = new SqlCommand(sSQL, (SqlConnection)con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    IDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
             }

After a while the program is able to recover and continue its task. I would consider this a duplicate to this but I am not using database mirroring and this is not happening on connection.open

I can increase the time out from the default but I am not real sure this should be a issue.

If you have multiple threads calling the same sproc, check if there are deadlocks in SQL. — Eric J., Jul 11 '12 at 19:53
Only minor but you can use reader.hasrows(). I am more interested in in the inserts. Does the select ever lock when you have no inserts taking place? And have you compared the speed to just holding a connection open and doing the select serial? A thread, connection, command, and reader is a lot of overhead. How long does that query take? — paparazzo, Jul 11 '12 at 21:57
@Blam with all the processing I am doing along with reading from a XMLFile and reading 1500 records I can do it in about 25 seconds — Micah Armantrout, Jul 11 '12 at 23:08
OK but that was not my question. How long does one the query take? Put a stop watch around cmd.ExecuteReader() and reader.Read(). — paparazzo, Jul 11 '12 at 23:13
@MicahArmantrout Have you managed to fix it? How? Pls advise. Perhaps add an answer. — Krunal, Dec 04 '13 at 05:31
Sorry I do not have a answer otherwise I would have provided one — Micah Armantrout, Dec 04 '13 at 19:49
@MicahArmantrout Did you ever sort out this problem? It sounds interesting. — Ann L., Aug 30 '14 at 02:29
@MicahArmantrout Were you able to solve this? I'm having a nearly identical problem except my query only takes 5 seconds and increasing Connection Timeout and Command Timeout as well as setting a Min Pool Size=20 to the sql connection string have not helped. — Jeff, Nov 09 '17 at 20:26

Multiple Simultaneous SQL Connection Timeouts (Threading)

0 Answers0