Is it possible for SQL Server to miss rows on a select * where PK = "value" query

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?

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.

StackExchange Database Administrators Q#71626, answer score: 9