1

We are terribly confused with a recurring error on our client's machine. I have a long running application that processes ~6million records over about 20 hours. On our dev environment locally everything works, the process completes successfully each day. On our client's machine, however, we continue to get a timeout.

The program simply uses a Parallel.Foreach on each record to spawn a thread and process the record, performing a few queries and inserts.

In the client's sql log we are seeing:

2013-11-21 09:02:12.10 spid4s      SQL Server has encountered 4 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\Program Files...
2013-11-21 09:02:12.10 spid4s      SQL Server has encountered 5 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\Program Files...
2013-11-21 09:18:02.35 spid4s      SQL Server has encountered 10 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\Program Files...

In my C# application's log file I recieve the error:

------ERROR------
Time : 11/21/2013 9:05:14 AM  
Source : .Net SqlClient Data Provider
Module : SqlInternalConnection
Method : Void OnError(System.Data.SqlClient.SqlException, Boolean)
Message : Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Occurring At : 

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

And in the few traces the client has been able to give us, nothing looks out of the ordinary, until the queries start timing out. We don't see a steady climb in query time or any noticable pattern.

We've added with (nolock) to our queries to ensure we aren't deadlocking, even though the sql log didn't report any. The machine has 16GB of memory on it, and sql's max is set ~14GB.

Essentially I am wondering if anyone can assist me in beginning to locate the problem. It doesn't occur at the same time throughout the day, and never at the same record (between 200k and 1.5 million have been processed before a crash on different runs).

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Jonesopolis
  • 25,034
  • 12
  • 68
  • 112
  • This could be a sessiontimeout. The default is 60 seconds but it can be set in the connectionstring. – Paul Sinnema Nov 21 '13 at 19:30
  • Unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on [so]. See "[Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?](http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts). – John Saunders Nov 21 '13 at 19:33
  • http://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation – P.Brian.Mackey Nov 21 '13 at 19:33
  • Timeout is different by deadlock. Check your timeout in connection string, then in SQL Server, then in IIS (if it is the case), in DCOM+ (if necessary), etc. Those 15 seconds seems to be default value of a ADO.NET connection string timeout. – bjnr Nov 21 '13 at 19:39
  • Yeah, the timeout is 15 seconds. But these are very simple inserts and selects. The same queries run thousands of times before at ~100ms each. I'm curious as to the cause of the huge performance hit – Jonesopolis Nov 21 '13 at 19:42
  • Most likely there is a storage device driver issue on your clients server. – EkoostikMartin Nov 21 '13 at 19:52
  • But you defenitly get a timeout so the query is taking longer than the 15 seconds. Did you try and use the SQL server profiler to see what is going on? – Paul Sinnema Nov 21 '13 at 19:53
  • Another thing. Is your client doing things next to this run? If so there might be a flaw in one of the other application accessing the DB causing a lock in the DB (coffeebreak syndrom). – Paul Sinnema Nov 21 '13 at 19:55
  • You should also check the SQL Server logs for anormalities – Paul Sinnema Nov 21 '13 at 19:57
  • Is this something you can do with a stored procedure, or using SQL Server Integration Services? Probably wouldn't take 20 hours, and would save you the connection issues. Just because you can do it in C# doesn't mean it's necessarily a good idea to do so. – criticalfix Nov 21 '13 at 20:01
  • If there were any deadlock on the server I should see it in the server log right? It has no logs for hours on either side of my application crash. And yes like i said we got traces from the profiler back, and couldn't see any pattern to it – Jonesopolis Nov 21 '13 at 20:05
  • What about my other remarks (SQL Profiler, Client working on same DB)? – Paul Sinnema Nov 21 '13 at 20:16

0 Answers0