-1

The following query usually returns a large number of results (somewhere around ~750k results). However, in production, I encounter a sporadic situation where it returns 0 results. If we were to re-run it the next second after it returned 0, it would just return the results as normal.

I am sure that the data is still there and that there shouldn't be anything that would delete it...

I had a look in a few places and there's no way I could find what might be causing the issue mentioned above ... also, there doesn't seem to be an easy way to replicate it... it's just random.

As my SQL knowledge is quite limited, could you please think of certain scenarios where that might occur? Or maybe suggest easy ways of debugging this? 

SELECT MAX(Table1.Cust_ID) AS cust_id, Table1.Email, MAX(Table1.Cust_Name) as cust_name
FROM dbo.Table1 WITH (NOLOCK)
INNER JOIN dbo.Table2 WITH (NOLOCK)
  ON Table1.id = Table2.id
  WHERE Table1.some_id = 100 AND Table1.some_value = 'test_value'
  AND  Table1.some_boolean_1 = 1
  AND  Table1.some_date_1 IS NULL
  AND (Table1.some_boolean_2 IS NULL OR Table1.some_boolean_2 = 1)
  AND (Table1.some_date_2 > '20171119' OR Table1.some_date_3 > '20171119' OR 
       Table2.some_date_3 > '20171119')
GROUP BY Table1.Email

Many thanks!

Catalin Lupuleti
  • 37
  • 10
  • Go line by line and check all those WHERE clauses and find where the records fall off? Query the tables every few seconds and confirm records are not being mass deleted? – dfundako Nov 19 '18 at 16:04
  • You put it in the title so I got to ask, do the results disappear too when you don't use (NOLOCK)? Dirty reads - data file shifting? – Mazhar Nov 19 '18 at 16:05
  • 9
    This may well be "working as designed". `NOLOCK` can skip rows, repeat rows and (in older versions, at least) error out as a result of data movement, especially under load. If you need reliable results, don't use `NOLOCK` -- stick with `READ COMMITTED`, or use snapshot isolation. `NOLOCK` is only remotely useful for queries where the correct results don't really matter (for example, monitoring queries that will be re-run momentarily). – Jeroen Mostert Nov 19 '18 at 16:06
  • 4
    Yes, `nolock` can skip rows. See https://dba.stackexchange.com/a/71640/5203 and the links from there. – GSerg Nov 19 '18 at 16:06
  • @dfundako -> I am convinced that it's not due to any of the WHERE clauses ... I am thinking if it could be an issue around other transactions putting a lock on those values while updating them – Catalin Lupuleti Nov 19 '18 at 16:06
  • @Jeroen I would expect only some of the rows to be omitted, while here is the case that the query comes back with no results at all; I don't necessarily want to get the most up-to-date / reliable data; the role of the NOLOCK is to ensure I am not putting locks while reading from this table (given that reading so many rows can take a while and also given that this table is a replicated / used for reporting; therefore many other users are querying it; Thanks a lot for your help! – Catalin Lupuleti Nov 19 '18 at 16:13
  • 2
    `NOLOCK` performs a "raw" read on the page structures. It is not only not consistent on the row level, it's not consistent at all. It is *absolutely* possible for it to skip *all* rows you're interested in, if it comes in in the middle of a page split. Source: personal experience from a query that did a bunch of `COUNT(*)`s with a `GROUP BY` that suddenly started reporting that my table was completely empty when the server was under heavy load (when, obviously, it was not). This issue only occurred once, but it was serious enough that it justified a switch to snapshot isolation. – Jeroen Mostert Nov 19 '18 at 16:15
  • 1
    Your query is syntactically incorrect - perhaps caused by your obfuscation attempts? You cannot select 3 columns and group by just one of those. – SMor Nov 19 '18 at 16:41
  • @SMor Only one column is not inside of an aggregate function. – GSerg Nov 19 '18 at 16:58
  • @GSerg Only now after the question has been changed. – SMor Nov 19 '18 at 18:42

1 Answers1

-2

The WITH(NOLOCK) hint makes SQL Server execute the query in a non-transactional way, meaning data integrity is ignored, and results can be any kinds of intermediate state. Same effect as setting the transaction isolation level to READ UNCOMMITTED, but for this query only. Results can include also inserted, but not yet committed rows, and anything running besides a transaction. Not sure what causes the empty result, but it can return strange results when modifications run at the same time.

Erik Hart
  • 1,114
  • 1
  • 13
  • 28
  • 3
    I didn't downvote your answer, but the opening line, `The WITH(NOLOCK) hint makes SQL Server execute the query in a non-transactional way` is at best misleading and at worst incorrect. `WITH(NOLOCK)` ignores the state of other open transactions on the underlying table, but that's hardly "non-transactional". – Eric Brandt Nov 19 '18 at 16:44