ok I have it now. I saw Clustered Index Seek and without looking deeply enough was assuming it was using both columns in the Seek predicate but of course it...

if you use the scripts that I attached to the first post you whould be able to see for yourself. Yes in this small sample of data 100% of rows...

you see I thought that I would be retrieving most of the time using where recordstatus_id = 0, and that because this was a clustered index then all the recordstatus_id...

the problem is that although I am not updating or inserting the record I am trying to select, I am still being blocked due to this key lock (in the...