Tip of the Month: WAIT_AT_LOW_PRIORITY option for Index Rebuilds

By Lori Brown | SQL Administration

Sep 03

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

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>