0

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

4
  • 1
    Why do you need to reorganize the index? – Erik Darling Sep 10 '18 at 17:44
  • @sp_BlitzErik it was intended to a be a more lightweight defragmentation than a full REBUILD. The index maintenance job reorganizes indexes between 10-30% fragmentation, and rebuilds indexes over 30% fragmentation – Brian Sep 10 '18 at 18:49
  • Yeah nah don't do that. – Erik Darling Sep 10 '18 at 18:50
  • @sp_BlitzErik thanks! That pretty much confirms my suspicions – Brian Sep 10 '18 at 20:17
5

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.

1
  • Sounds good to me. And I discovered that the seemingly-unrelated tables were being locked because the whole index maintenance job was being done in a transaction (to be fair, the transaction was not being started intentionally; it was an unintended side-effect of EntityFramework 6) – Brian Sep 10 '18 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.