Sql-server – REORGANIZE INDEX blocks other queries

azure-sql-databasesql server

(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?

Best Answer

Unfortunately, your understanding is a misunderstanding, and REORGANIZE requires locks for its operation. It just doesn't have as severe locking as an offline REBUILD.

Since REORGANIZE takes X locks, it has the potential to block readers.

Here's a quick demo to prove that it takes locks.

DROP TABLE IF EXISTS dbo.whyreorg

CREATE TABLE dbo.whyreorg
(ID INT IDENTITY PRIMARY KEY, junk INT)

INSERT dbo.whyreorg
SELECT TOP 1000 FLOOR(RAND(CONVERT(VARBINARY,NEWID()))*1000)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b

CREATE INDEX IX_whyreorg_this ON dbo.whyreorg (junk)

BEGIN TRAN
ALTER INDEX IX_whyreorg_this ON dbo.whyreorg REORGANIZE

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

COMMIT

Sample output (trimmed for space): Locks from REORGANIZE

REORGANIZE shouldn't take locks on other tables, so I would hypothesize something in the blocked transactions in dependent on the table undergoing reorganization. Without details, it's impossible to confirm this.

Thankfully, the solution here is simple: stop reorganizing.

Related Question