Fork me on GitHub

I'm having database issues with C# calling into SQLT on sql-server. A good number of times a day I will get an error that a query which 100% should return rows, will return 0 rows. For each individual query, it runs each 48 times a day, sometimes a given query x will fail and return 0 rows only 2 or 3 times a day. The data in the db isn't changing, so if it succeeds once, it should succeed every time. I get no errors when this fails like you might expect if it was a concurrency issue, e.g. deadlock errors etc. Just the query returns 0 rows. I can reproduce it occasionally on debug, but its very sporadic, where I will breakpoint in the if condition for no rows, then just rerun the query by dragging breakpoint to the db call, and it works and I get rows... Has anyone seen this?


maybe your setup has multiple nodes and you sometimes hit a different node/replica with inconsistent data via some kind of load balancer?


nah, its hitting one single on-prem db. No replication, no load balancing.


Perhaps there’s a fault tolerance thing in place where it returns 0 rows if something in particular errors out. We had a similar situation where sometimes the response to the front end would have 0 elements in the array because there was some code facilitated by Hystrix that would return an empty array if a particular call to one of micro services errored out.


This is in the backend service, and its the object that shows 0 rows. I'm not even using an ORM.


I';m right in thinking it can't possibly be our sql server having too many requests at once and jsut returning some as empty result set. That would be crazy right?


That would be crazy


Cause I can literally stick a breakpoint on the line

(if rows.Count == 0)
    // print error.
And then drag it back a couple of lines previous to execute the sql, and it will return rows.


I dont even know where to start to debug this


Or maybe as a temp hack fix, I say if rows.Count == 0, then just rerun the query. And if it fails twice then error.


That seems a terrible solution though.


Can you check the DB logs for whether there are queries where it would return 0 rows?


Since you can repro in local, there won't be a lot of logs that you'll need to scan through


This sounds so much like what I used to see while talking to MS-SQL Server in the late 90s. I’m sure it’s better by now, but it came down to thread races (in MS code… my code was single-threaded), and by adding a small delay (or reordering some operations) I was able to hide the problem. I’m sure this isn’t your issue, but you reminded me about it when you said ADO.


Maybe an obvious question, but you’ve ruled out any sources of non-determinism in the query itself, e.g. no current date/time, user-defined functions, non-deterministic built-ins, etc?


yeah, the query is basically

select foo, bar, quax
inner join ...
left join ...
inner join ...
left join ...
where hotdog = @someparam
WHat I hve noticed is, we have a greatly reduced load going through the database (only around 40k an hour right now, whereas normally its an order of magnitude greater) and a lot of these row counts = 0 have just stopped happening