0

I want to rebuild or reorganize indexes in a table. What happens if there are active connections to that table at the moment?

1

I want to rebuild or reorganize indexes in a table

These are two different modes to remove index fragmentation.

  1. Rebuilding an index means that a whole new set of pages is allocated for it.

    It cames in two flavours: online and offline. When rebuilding offline your index is completely unavailable, but the operation is faster than that online. It can be minimally logged (in simple or bulk logged recovery models), and even when it's fully logged it's logged very efficiently (the whole index pages when built are written to log).

    Online rebuild is available only in SQL Server Enterprise, Developer, and Evaluation editions. The operation is always fully logged and produce a notable amount of log as new index is logged row by row. Besides, more exact term for it is "almost online operation". Yes there is no lock held for the entire duration of the index rebuild operation, but the locks are still held.

    Here is a great explanation of Paul Randal:

    Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)

    Myth 5: online index rebuild doesn't take any locks

    This myth is untrue. The 'online' in 'online index operations' is a bit of a misnomer. 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.

    The most recent time this came up on the forums was someone noticing insert queries timing out after an online index rebuild operation had just started. The problem is that the table lock that online index rebuild needs has to be entered into the grant queue in the lock manager until it can be acquired – and it will stay there until existing transactions that are holding conflicting locks either commit or roll-back. Any transaction that requires a conflicting lock AFTER the index rebuild lock has been queued but not acquired (and then released) will wait behind it in the lock grant queue. If the query timeout is reached before the transaction can get it's lock, it will timeout.

  2. Reorganizing is designed to remove logical fragmentation from the leaf level of an index while keeping the index online and as available as possible. When reorganizing an index, SQL Server acquires an Intent-Exclusive lock on the index B-tree. Exclusive page locks are taken on individual pages only while those pages are being manipulated. So you can consider reorganizing an online operation.

0

If you make online rebuild, SQL Server will make like a replica index and will replace the old one. When doing that replace, it will get an schema modification lock.

If you got an active connection in that moment, it is possible to happen even a deadlock when the other connection is making an update. It's not common, but there is a possibility.

You can check other considerations here https://docs.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations

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.