This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2014 introduced WAIT_AT_LOW_PRIORITY option which enhanced the performance of ONLINE REBUILD INDEX.
In the previous version of SQL Server, REBUILD INDEX had to wait for the running transaction.
But now in SQL Server 2014, you can give options like MAX_DURATION in minutes, ABORT_AFTER_WAIT with different options.
For ABORT_AFTER_WAIT, took the reference from Microsoft:
|
1 |
ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS] |
NONE: Continue waiting for the lock with normal (regular) priority.
SELF: Exit the online index rebuild DDL operation currently being executed without taking any action.
BLOCKERS: Kill all user transactions that block the online index rebuild DDL operation so that the operation can continue. The BLOCKERS option requires the login to have ALTER ANY CONNECTION permission.
Please check the below demonstration, where I am comparing old and new approach of REBUILD INDEX:
Create a sample table:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_DumpData ( ID INT ,RandomID VARCHAR(MAX) ,CONSTRAINT pk_tbl_DumpData_ID PRIMARY KEY(ID) ) GO |
Insert dummy data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
;WITH CTE AS ( SELECT 1 ID UNION all SELECT ID + 1 FROM CTE WHERE ID + 1 <= 100000 ) INSERT INTO tbl_DumpData(ID,RandomID) SELECT ID ,NEWID() AS RandomID FROM CTE OPTION (MAXRECURSION 0) GO |
Open new session/query window, execute below update:
|
1 2 |
BEGIN TRANSACTION UPDATE tbl_DumpData SET RandomID = 39840972340234 WHERE ID = 8 |
Jump to old session window, and execute below REBUILD:
It is Running… Running… Running…, because table is locked by another transaction
|
1 2 3 4 5 6 |
ALTER INDEX pk_tbl_DumpData_ID ON tbl_DumpData REBUILD WITH ( ONLINE = ON ) GO |
Check the lock table:
You can find that your REBUILD query is in WAIT mode.
|
1 |
SELECT *FROM sys.dm_tran_locks |
Now stop the execution of the previous REBUILD, and execute below REBUILD WITH WAIT_AT_LOW_PRIORITY:
Set MAX_DURATION for one minute and ABORT_AFTER_WAIT type is SELF.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
ALTER INDEX pk_tbl_DumpData_ID ON tbl_DumpData REBUILD WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 ,ABORT_AFTER_WAIT = SELF ) ) ) GO |
Above REBUILD will wait for One minute and then it will throw an error message:
|
1 2 |
Msg 1222, Level 16, State 56, Line 1 Lock request time out period exceeded. |
Now, test with BLOCKER option:
It will wait for one minute, and then it will kill the open UPDATE transaction to finish the REBUILD operation.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
ALTER INDEX pk_tbl_DumpData_ID ON tbl_DumpData REBUILD WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 ,ABORT_AFTER_WAIT = BLOCKERS ) ) ) GO |
Now, go to another transaction where UPDATE transaction was there:
Try to execute any query, you will get below error message because this session is terminated by REBUILD + BLOCKERS.
|
1 |
SELECT *FROM tbl_DumpData |
Error:
|
1 2 |
Msg 0, Level 20, State 0, Line 0 The connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. No attempt was made to restore the connection. |
Leave a Reply