— By Lori Brown @SQLSupahStah
Available in SQL Server 2014 Enterprise Edition since can only be done using ONLINE rebuilds.
Index rebuilds have been enhanced with the WAIT_AT_LOW_PRIORITY option. It is used to set the duration in minutes to wait as well as behavior when blocking happens. When setting WAIT_AT_LOW_PRIORITY you also must set MAX_DURATION and ABORT_AFTER_WAIT to tell SQL how long to wait for blocking and then what to do after that amount of time has expired and blocking is still happening.
ALTER INDEX ix_RoadRaces_State
on dbo.RoadRaces
REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = SELF))