I want to rebuild or reorganize indexes in a table. What happens if there are active connections to that table at the moment?
I want to rebuild or reorganize indexes in a table
These are two different modes to remove index fragmentation.
Rebuildingan index means that a whole new set of pages is allocated for it.
It cames in two flavours:
offline. When rebuilding
offlineyour index is completely unavailable, but the operation is faster than that
online. It can be
bulk loggedrecovery models), and even when it's fully logged it's logged very efficiently (the whole index pages when built are written to log).
Onlinerebuild is available only in SQL Server
Evaluationeditions. The operation is always
fully loggedand 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:
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.
Reorganizingis 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-Exclusivelock 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
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