SQL Server Read Uncommitted is blocking transactions

Question

I have a work queue in SQL, managed by some services that read out entries to process with READPAST queries.

I recently added a UI to check on queue status that uses READ UNCOMMITTED NHibernate queries in C#. It's not at all critical that they give me the right numbers back, just an indication of how far along the processing is.

/* called from a foreach (I know... I could get them all at once with SQL) */
IQuery query = Persistence.CreateQuery(
    "select count(qi) from QueueItem qi where qi.Job = :job");
query.SetEntity("job", thisJob);
using(Persistence.ReadUncommitted())
{
    return (long)query.UniqueResult();
}

The problem is that these status queries have started causing timeouts. Sometimes they fail themselves; sometimes they cause queue operations to fail.

The main MSDN documentation says there can still be locking if I change the schema, which I am not.

On the other hand, Marcel van der Holst who apparently works for Microsoft's SQL Server team, says this on the subject (the Michael asking the question is not me):

READ UNCOMMITTED transactions will not take any database locks, but will still have to read databases pages to read the actual data. If other transactions are writing these pages at the same time, their might be some blocking between the two. Internally in the engine, we do not allow any transactions to read a page while a write is being in progress (we use latches to guarantee this). If a lot of transactions are writing while your big queries are going on, the big read might still become blocked.

Am I doing something wildly wrong? What should I change to stop blocking?

Schema

create table QueueItem(
    ID int identity(1,1) not null,
    JobID int not null,
    PersonID int not null,
    DateProcessed datetime null,
    Error varchar(max) null,
    constraint [PK_QueueItem] primary key nonclustered (ID)
)

alter table QueueItem
add constraint [FK_QueueItemsToJobs] foreign key (JobID)
references Job (ID)

Nonclustered indices:

JobID, DateProcessed, PersonID, ID (Non-Unique, Non-Clustered)
DateProcessed, JobID, PersonID (Non-Unique, Non-Clustered)
JobID, ID (Unique, Non-Clustered)
JobID, PersonID, ID (Unique, Non-Clustered)
PersonID, JobID, ID, DateProcessed (Unique, Non-Clustered)
ID (Unique, Non-Clustered)

In Practice

I have reduced the number of reads because it's not going to make things slower, but I'm still curious why there might be blocking with READ UNCOMMITTED.

Answer 1

I'm not an expert on NHibernate, but you should try to add NOLOCK to your status queries; use SetLockMode in NHibernate.

1. Reduce the number of indexes, looks like every column is indexed.
2. You are not using a clustered index, add a unique clustered index on the columns JobID, ID.