Index maintenance operations that are ONLINE in Standard Edition: Is table Read-Write or Read-Only?

Posted on

Question :

As we understand it, related to SQL Server 2008 and above:

ALTER INDEX ... REORGANIZE

Is on ON LINE operation in ALL flavors of SQL Server.

ALTER INDEX ... REBUILD

Is a BLOCKING operation, unless you have SQL Server Enterprise Edition…. (and even with the Enterprise Edition, is blocking when rebuilding clustered indexes)

If the above is all correct, the question is:

During an ALTER INDEX ... REORGANIZE operation, is the table available for read and write, or are writes blocked?

Answer :

During an ALTER INDEX … REORGANIZE operation, is the table available for read and write, or are writes blocked?

REORGANIZE is always a online operation and single threaded operation but does not holds blocking locks. When you reorganize there is option to allow_page_locks =ON or OFF if you keep this value off you might get error like index cannot be reorganized as it cannot take page level lock so its better to reorganize index with option allow_page_locks =ON. So yes table is available for read write operation locks are taken as pointed in the option but as I said it does not holds blocking locks

As per Microsoft Books Online

Turning off page and row locking might or might not be acceptable because the weekly batch update will block the concurrent readers from accessing the table while the update runs. If the batch job only changes a few rows or pages, you can change the locking level to allow row or page level locking, which will enable other sessions to read from the table without blocking. If the batch job has a large number of updates, obtaining an exclusive lock on the table may be the best way to ensure the batch job finishes efficiently.

ALTER INDEX … REBUILD
Is a BLOCKING operation, unless you have SQL Server Enterprise Edition…. (and even with the Enterprise Edition, is blocking when rebuilding clustered indexes)

Even index rebuild in enterprise edition do takes short term locks as per Paul Randal Article Online index operations need to take two very short-term table locks. An S (Shared) table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification – think of it as an Exclusive) table lock at the end of operation to force all read and write plans that could touch the index to recompile.

Leave a Reply

Your email address will not be published. Required fields are marked *