SQL Server 2014 news – rebuild index online with WAIT_AT_LOW_PRIORITY

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.

/Håkan Winther
Twitter: @h_winther