3

I have a custom application that builds a Lucene Index on our various SQL Server VMs hosted in Azure (it runs locally on the machine. I know this isn't ideal, but reasons). This program has run flawlessly on multiple machines for years.

Recently we built a new VM using Windows Server 2016 and SQL Server 2016. Now the program seems to hang randomly when calling reader.read() until I kill the process. This has never been an issue before, but the versions of Windows and SQL Server were mainly 2012 or lower before this.

When I run the application on a different machine and point it at the new SQL Server 2016 VM, it works fine. When I run the application locally on the new VM and point it at itself, it will fail. It will also fail if I point it at a previously working SQL Server while it runs locally on the new VM.

This almost makes me believe it's more of an issue with Windows 2016 and not SQL Server 2016.

This just uses the SqlCommand class in pretty much the most basic way. (I'm just typing this as an example, this is not the actual code).

using (SqlConnection connection = new SqlConnection("connection String"))
{
     connection.Open();

     using (SqlCommand cmd =  new SqlCommand("query", connection)
     {
          connection.Open();

          using (var reader = cmd.ExecuteReader())
          {
               // freezes here randomly
               while (reader.read())     
               {
                   // do stuff
               }
           }
      }
}

Besides freezing the only wait I get from SQL Server is mainly from 'Network I/O'. This is because SQL Server has processed the rows from the query, but the application suddenly starts to fail pulling them.

I've tried everything I can think of so If anyone has any suggestions, I would greatly appreciate it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rich
  • 39
  • 1
  • huh, that's odd; does it ever timeout? or does it hang there forever? – Marc Gravell Apr 02 '19 at 19:32
  • i have a timeout = 0 because this is an intense query. So no it doesn't time out, but its probably because of that. However, I've seen it freeze on the first row and 50K rows in. It's so random. – Rich Apr 02 '19 at 19:40
  • what .NET version are you targeting here, out of curiosity? – Marc Gravell Apr 02 '19 at 19:41
  • I've tried 4.5, 4.5.1, 4.5.2, 4.6.1 and 4.6.2 – Rich Apr 02 '19 at 19:47
  • i've also tried connecting through shared memory and tcp. I've tried various compability modes as well. Same issue. – Rich Apr 02 '19 at 19:48
  • well, I've got to say: this is unusual; I haven't seen this, but that doesn't mean it isn't there. Is this a particularly "fat" query? are you returning huge blobs/clobs at all? or just regular simple columns? – Marc Gravell Apr 02 '19 at 20:49
  • `I'm just typing this as an example, this is not the actual code` It is key that the code you shows us reproduces the issue. Are you saying you are able to reproduce the issue **with that code**? – mjwills Apr 02 '19 at 21:20
  • actually yes - besides calling the actual stored procedure and using the correct connection string... with all other logic commented out it will still happen. My co-worker actually found another post that is very similar to the issue i am having. https://dba.stackexchange.com/questions/189522/are-there-known-parallelism-issues-with-sql-server-2016 – Rich Apr 03 '19 at 12:17

0 Answers0