2

I run a process built in C# .Net 4.6.1 that accesses a SQL Server database. The process runs on Windows Server 2012 R2 Standard. The SQL Server database is on another server with SQL Server version 11.0.6567.0

The process executes the following code:

01  var readStatement = “select * from TableA order by ColumnX,ColumnY, ColumnZ offset 0 rows”;   // TableA has 13 million rows
02  var readCommand = new SqlCommand(readStatement, myConnection) { CommandTimeout = 72000 };
03  var reader = readCommand.ExecuteReader();
04  while (reader.Read())
05  {
06     foreach (var columnName in columnNames)
07     {
08        Console.WriteLine(“Checkpoint 1”);
09        var value = reader[columnName];
10        Console.WriteLine(“Checkpoint 2”);
11     }
12  }

This process normally completes with no problem. Occasionally, it goes into line 09 (a call to SqlDataReader.GetValue) and never returns. I was able to witness this problem while running a trace on the database. The process went to that call at about row 8 million (out of the 13 million), but never returned. At the time it hung, the database trace reported “Batch Completed” and then “Audit Logout”. I am unable to replicate the problem in the debugger to see how deep into SqlDataReader.GetValue the process is hanging. It does not run into memory issues or other resource constrain

Here is the call stack to where the process hangs:

System.Data.dll!SNINativeMethodWrapper.SNIReadSyncOverAsync Normal [Managed to Native Transition]
System.Data.dll!SNINativeMethodWrapper.SNIReadSyncOverAsync(System.Runtime.InteropServices.SafeHandle pConn, ref System.IntPtr packet, int timeout)
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadByteArray(byte[] buff, int offset, int len, out int totalRead)
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadDouble(out double value)
System.Data.dll!System.Data.SqlClient.TdsParser.TryReadSqlValueInternal(System.Data.SqlClient.SqlBuffer value, byte tdsType, int length, System.Data.SqlClient.TdsParserStateObject stateObj)
System.Data.dll!System.Data.SqlClient.TdsParser.TryReadSqlValue(System.Data.SqlClient.SqlBuffer value, System.Data.SqlClient.SqlMetaDataPriv md, int length, System.Data.SqlClient.TdsParserStateObject stateObj, System.Data.SqlClient.SqlCommandColumnEncryptionSetting columnEncryptionOverride, string columnName)
System.Data.dll!System.Data.SqlClient.SqlDataReader.TryReadColumnInternal(int i, bool readHeaderOnly)
System.Data.dll!System.Data.SqlClient.SqlDataReader.TryReadColumn(int i, bool setTimeout, bool allowPartiallyReadColumn)
System.Data.dll!System.Data.SqlClient.SqlDataReader.GetValueInternal(int i)
System.Data.dll!System.Data.SqlClient.SqlDataReader.GetValue(int i)

David Stevens
  • 173
  • 1
  • 1
  • 8
  • Almost seems like an exception occurred but was not raised. Probably not related but what is the purpose of the `OFFSET 0 ROWS` clause in the SQL query? – Dan Guzman Aug 31 '17 at 19:45
  • It takes about 2 hours to get to row 8 million. My command timeout parameter is set to 20 hours. It is not running on a webserver, just an .exe running at a command prompt. – David Stevens Sep 01 '17 at 19:37
  • I use the offset qualifier in the query because the number of rows to offset is determined dynamically by the process. It just happened to be 0 in this case. – David Stevens Sep 01 '17 at 19:38
  • I still think this has something to do with some kind of timeout, I would put a index on `ColumnX,ColumnY, ColumnZ` so the data will be presorted for the query. Other than that, I am not sure. I would do more logging on the sql server to check if any kind of server side exception happened. – Scott Chamberlain Sep 01 '17 at 21:07
  • I think you are right, Scott, that an index might solve whatever the problem is. I added a clustered index to another process that was showing the same problem and the problem went away. The caveat is that the problem has gone away before for no apparent reason, so not seeing it this time is no gaurantee. I have many processes that run daily and I see this problem in one or more of them a couple times a week. If I don't see it at all in the next month or so, I will have strong evidence the index fixed it. I will post an update based on what I see. – David Stevens Sep 01 '17 at 21:24
  • I don't think it is a network issue because I have a number of other processes communicating across the same lines that show no problem. – David Stevens Sep 01 '17 at 21:25
  • Adding the clustered index did not resolve the problem. – David Stevens Sep 02 '17 at 22:08
  • You might want to look in to [doing data tracing](https://msdn.microsoft.com/library/hh880086.aspx) to see what is going on at the moment of error – Scott Chamberlain Sep 03 '17 at 23:55
  • I did set tracing on the SQL Server side. At the time it hung, the database trace reported “Batch Completed” and then “Audit Logout”. – David Stevens Sep 05 '17 at 01:15
  • Did you ever resolve this? I'm getting the exact same stack trace for seemingly random queries at random times. It feels like either a network issue or a framework bug as I feel like I've ruled everything else out. https://stackoverflow.com/questions/55396666/need-help-diagnosing-sql-server-strange-query-timeouts-from-c-sharp – Stu Price Apr 03 '19 at 12:13
  • No, this was never resolved. – David Stevens Apr 04 '19 at 13:03

0 Answers0