0

We are experiencing a strange problem we weren't able to reproduce as it is only happening sometimes.

The issue is that sometime a query returns empty when it should contain data. If run again, it normally returns the data you would expect it to. It seems that it mostly happens when the server is under high load.

We are using an ORM in .NET and connecting to a Microsoft SQL Server. It doesn't matter which Version of the SQL Server we are using, we experienced it through every version we ever used (2012 to 2017). As an ORM we were using Nhibernate until now but as we are porting the whole project to .NET Core we switched to Entity Framework Core and while testing something I experienced this problem once with EF Core 3.

Multiple people checked our code and could not find anything. I also wasn't able to replicate the issue with simply producing 100% load on the machine by running prime95 or something similar.

For one of our installations we switched to PostgreSQL and the problem never came up there. One of my colleagues thinks he remembers having the issue once in the SQL Management Studio, but that never occured again.

Has anybody seen this or something similar and/or knows how to fix it?

2
  • When queries time out you get no data back. Can increase the timeout and check code to make sure you are not eating exceptions. – Brian Jan 29 '20 at 20:02
  • I checked again and no, we are not catching any exceptions at this point. I saw query timeout exceptions often enough in other parts of the system (before we optimized them) so it is also not like we would always hide them. – Christoph Jan 31 '20 at 11:01
0

If the query uses functions like GETDATE() try to replace those functions by variables and set those variables before running the query.

Using functions like GETDATE() or other SQL functions in the where clause means output of the query may change every time you execute the query even if the data is unchanged.

1
  • Unfortunately this is not the case. None of the queries the ORM generates is using any functions (at least in the queries we are having problems with). – Christoph Jan 10 '20 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.