March 12, 2009

Exclusive lock on a particular table for select * from statement

select * from statement gets an exclusive lock on table which is having less than 10K rows . Activity monitor shows lock by processes for an objects named dbname.dbo.spt_fallback_db.
spt_fallback_db is in master and is a system table.
The issue get solved when reorganized the index in that table.
The optimizer will lock objects if it deems it will yield the best query performance. Out of date statistics and a fragmented index are a prime combo for this type of thing

No comments: