At last Microsoft released a SQL Server edition that supports online rebuild of a single…
You can rebuild your indexes online in SQL Server Enterprise edition to prevent the rebuild operation from blocking other transactions, BUT the rebuild operation requires a lock at the beginning and the end. It can take a very long time to get a schema lock for an index with heavy load.
In SQL Server 2014, the rebuild index operation has been improved even more. Now you have the option to set a wait duration and action for what to do when the duration has passed. You can decide to continue to wait for the lock (NONE), abort the index operation (SELF) or transactions using the index (BLOCKERS). Try the following code:
CREATE TABLE dbo.x ( a INT IDENTITY(1,1) NOT NULL PRIMARY KEY, b DATETIME NOT NULL ); DECLARE @dt DATETIME =GETDATE(); INSERT INTO dbo.x (b) SELECT TOP 1000 DATEADD(MINUTE,ROW_NUMBER() OVER(ORDER BY o.name),@dt) FROM sys.objects o CROSS APPLY sys.objects O2 CREATE INDEX ixX ON dbo.x (b) --WITH(ONLINE=ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS))) BEGIN TRAN UPDATE dbo.x SET b=GETDATE() WAITFOR DELAY '00:02:00'; ROLLBACK TRAN
This code will create a table, populate it with some data, create an index an perform an update in a transaction that will take 2 minutes (thanks to the waitfor clause).
Open a second query window and run the index rebuild with a max duration of 1 minute:
ALTER INDEX IXX ON DBO.X REBUILD WITH(ONLINE=ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)))
As the update will take longer than the max duration of the index rebuild operation, the session with update will be killed. You can change the BLOCKSERS to SELF and try again.