5

We have just migrated an application to an SQL Server 2008 R2 from SQL Server 2005 and moved from a physical to virtual hosting environment.

I have a very strange error in our application and I was wondering whether anyone has seen a condition that could result in the following condition:

Table structure is very simple

CREATE TABLE [dbo].[TableName](
[PKCol] [nvarchar](8) NOT NULL,
[DataCol] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED 
(
    [PKCol] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TableName]  
WITH CHECK ADD CONSTRAINT [FK_OtherTable] FOREIGN KEY([PKCol])
REFERENCES [dbo].[OtherTable] ([PKCol])
GO

ALTER TABLE [dbo].[TableName] CHECK CONSTRAINT [FK_OtherTable]
GO

Query is:

SELECT PKCol, DataCol FROM TableName
WHERE PKCol = 'SomeString'

This query, for the last 5 years has returned the row for the tested strings. For a very short period of time (1 second in 2 weeks) this query is returning 0 rows (for a query string that exists 1 second before and 1 second after the query returns zero rows). If it helps there are a few dozen rows in this table and a search for ANY of them returns zero rows when the "condition" is occurring (as in the select query for different strings returns zero rows).

There are no inserts, deletes or updates being executed against the table. It is possible that a transaction is occurring on another table where this table is the foreign key.

The query is NOT returning any form of SQL error, it is a valid response from SQL which appears to indicate zero rows, which then affects the behaviour of the application code.

Any clues where I should start to look and/or KB/Hotfixes I should be looking at?

Improve this question
3
  • Is something shrinking the DB or reorganizing that index? That can move pages around. Commented Jul 16, 2014 at 13:36
  • Was there anything going on with the virtual hosting environment? maybe there was a restructuring of the Storage and pointers were being moved and for a split second things didn't match up. what time(local) was this one second interval? Commented Jul 16, 2014 at 18:39
  • OK, so index reorganise/shrink can cause issues? It's possible that the VM was being VMotioned to another node at the time. Commented Jul 17, 2014 at 1:20

2 Answers 2

Reset to default
9 
nvarchar(8) PK NOT NULL

... their PKs return null ...

This cannot happen, under any condition. Therefore I must conclude that your 'simplified' repro has simplified things beyond the limit of what you understand. Please post the exact table definition (including all indexes) and the exact query you issue.

Null is what comes back from the linq .FirstOrDefault() query in C#.

OK, that changes things. So, if we take your word for it, you see committed rows being skipped. Under dirty read isolation (or NOLOCK hint) this is a common phenomenon, see Previously committed rows might be missed if NOLOCK hint is used. Under higher isolation levels is rather uncommon to encounter missing rows, but not impossible, as Brent and Martin indicate in their comments.

So what are you to do? After all if you wanted to program against an eventually-consistent model you'd choose something cheaper and maybe faster. First ensure you are not under a dirty read isolation level. Anomalies at higher levels are rather uncommon, so lets make sure you're not in the common bucket. If you are positively satisfied that the read occurs under a higher than dirty reads isolation level, then you need to isolate the phenomenon. Again, post here the exact table definition and the exact query you run.

Read Understanding how SQL Server executes a query.

Improve this answer
8
  • 1
    Actually, have you seen Kendra's demo of the combination of index intersection and dirty reads? She demos a scenario where the data returned in the query looks like it violates constraints, but it's because the two index reads were done at different times. It's really slick - email her if you want more details on it. (It wouldn't happen in Spence's problem since he took out the nonclustered indexes, of course.) Commented Jul 16, 2014 at 20:14
  • 1
    @BrentOzar Craig Freedman has a similar demo for read committed here but don't think you could get a PK to return null with this type of issue. Commented Jul 16, 2014 at 21:37
  • 1
    @BrentOzar I don't think you can read a PK null, no matter of index order. For once the insert check occurs way before any write. And second to simply produce NULL result the output has to be nullable, there is simply no DBBINDING to capture a NULL on a non-nullable column. The probability of PBCAK is overwhelming. Commented Jul 16, 2014 at 22:17
  • Sorry, i'm a C# dev more than a SQL guy. I phrased my response incorrectly. What I meant to say is that the search for ANY value in the table returns zero rows. Null is what comes back from the linq .FirstOrDefault() query in C#. Commented Jul 16, 2014 at 23:48
  • Table structure as exported from SSMS. Commented Jul 18, 2014 at 1:53
6

If you're using READ UNCOMMITTED isolation level or the WITH (NOLOCK) in your queries, your queries can skip rows or see rows twice.

Kendra Little explains it in more detail in her 30-minute webcast There's Something About Nolock.

Improve this answer
7
  • 2
    Can this affect a table that (as the OP claims) has not seen any insertions, deletes or updates for a long time? (well, the OP claim is a bit contradictory as he has "just" migrated to new version, so all the tables are new and not years old) Commented Jul 16, 2014 at 9:49
  • The claims part is what's tough - if someone even just had a lock on a page, this can happen. I can conceive scenarios where someone takes a lock and doesn't change data, but it's skipped due to that isolation level. Commented Jul 16, 2014 at 13:14
  • NOLOCK is not READPAST. Locked rows are not skipped. Commented Jul 16, 2014 at 13:37
  • Right, but think about a transaction that does a delete (or moves the PK values, whatever) and then rolls it back while the select is running. Commented Jul 16, 2014 at 18:22
  • NOLOCK hint wasn't being used by this query. Commented Jul 17, 2014 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.