In the earlier versions of SQL Server, whenever you need to rebuild an index (pass DDL statement for an online index), you sometimes end up with deadlock situation. Given below is the script that we use to rebuild an online index in the earlier version of SQL Server.
USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
In SQL Server Hekaton, a solution to this problem has been introduced namely WAIT_AT_LOW_PRIORITY . It allows DBA to control the locking mechanism that is required to rebuild an online index and causing deadlock situations.
Given below is the script :
USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
DATA_COMPRESSION = ROW
)
;
In the above script, you can see two arguments MAX_DURATION and ABORT_AFTER_WAIT. Given below are the details.
MAX_DURATION :
It describes the waiting time in minutes, but you can just pass the integer number and MINUTES can be removed from the syntax.
ABORT_AFTER_WAIT:
ABORT_AFTER_WAIT came up with three nice options. It basically provides you the hands on the different locking mechanism. Given below are the details :
NONE : It implements no locking on the online index rebuild operation,it performs operation like a normal scenario.
Given below is the script if you would like to implement NONE in ABORT_AFTER_WAIT.
USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE ) ),
DATA_COMPRESSION = ROW
)
;
SELF: It aborts the online index rebuild operation by using normal priority. It gives priority to the user operation instead of rebuilding index.
Given below is the script if you intend to implement SELF.
USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = SELF) ),
DATA_COMPRESSION = ROW
)
;
BLOCKERS: It kills all the user transactions that usually block the online index rebuilding, so that you can rebuild index easily. This is not recommended if you are using at the peak hours.
Given below is the script if you would like to implement BLOCKERS.
USE AdventureWorks2012
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
FILLFACTOR = 80,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY
( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS) ),
DATA_COMPRESSION = ROW
)
;
Let me know if you implement it in CTP 1 and find any issues.
