WAIT_AT_LOW_PRIORITY option of Alter Index

2017-10-16

WAIT_AT_LOW_PRIORITY

Starting in SQL Server 2014, there is an option - WAIT_AT_LOW_PRIORITY for online index rebuild. We can use it to kill the blocking queries or stop the index rebuild job itself.

An online index rebuild has to wait for blocking operations on this table. WAIT_AT_LOW_PRIORITY indicates that the online index rebuild operation will wait for low priority locks, allowing other operations to proceed while the online index build operation is waiting. Omitting the WAIT AT LOW PRIORITY option is equivalent to WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

ALTER INDEX IX_My_Index ON dbo.MyTable REBUILD 
WITH ( 
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 1 MINUTES , 
        ABORT_AFTER_WAIT = BLOCKERS)));

This query gives any blocking SELECT queries currently running 1 minute to finish executing or else this query will kill them and then execute the index rebuild.

Alternatively we can do:

ALTER INDEX IX_My_Index ON dbo.MyTable REBUILD 
WITH ( 
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 1 MINUTES , 
        ABORT_AFTER_WAIT = SELF)));

The rebuild query would wait for 1 minute to check if there is still any blocking queries. If so, it would kill itself, allowing the other blocking queries to continue running.