Sample Header Ad - 728x90

REORGANIZE INDEX blocks other queries

1 vote
1 answer
5222 views
(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 dbo). 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?
Asked by Brian (113 rep)
Sep 10, 2018, 05:43 PM
Last activity: Jan 23, 2024, 09:39 PM