Tip of the Month: WAIT_AT_LOW_PRIORITY option for Index Rebuilds

— 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))

  • MAX_DURATION = time [MINUTES ]
  • ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]
  • NONE = Keep waiting
  • SELF = Give way to the user queries
  • BLOCKERS = Kill all user transactions that block the online index rebuild
Both comments and trackbacks are currently closed.
%d bloggers like this: