(SQL Server 12.0.2000.8 running in Azure)
It has been my understanding that REORGANIZEing an index should not interfere with other operations (that is, it should not block queries on the table undergoing index reorganization, and it certainly should not block queries on other tables). However, I have a nightly index maintenance job that appears to be blocking other queries while it runs.
The query causing the block is in the format:
ALTER INDEX [indexName] ON tableName REORGANIZE
It is causing other queries to wait, even simple ones like:
SELECT * FROM tableName WHERE indexedColumn = @value
I used the
sp_who2 procedure to see which queries were waiting, and which other query they were blocked by. And again, the table undergoing index maintenance and the table in the SELECT are completely unrelated (they're even in different schemas; FWIW the table being reorganized is in
The table being reorganized has almost 500 million rows. The index being affected is a non-clustered, non-unique index on a single bigint column used by a foreign-key. The table itself consists of two bigint columns, one tinyint, and a couple small nvarchars.
Doesn't seem like anything too extraordinary, but I cannot figure out why it's blocking other queries. Is there some hidden dependency that I'm missing?