My first post on this blog detailed a scenario where a read uncommitted select statement could ultimately block an insert/update/delete. In this scenario, a long running read uncommitted select is executed requiring a schema stability lock. That lock prevented the online rebuild from grabbing the schema modification lock necessary and caused the update statement to get queued up behind it.

SQL Server 2014 introduced an option that will allow more control over how online rebuilds behave in a scenario such as the one I described. The WAIT_AT_LOW_PRIORITY option gives you a few different choices in dealing with blocking scenarios. The syntax, from Books Online, is below.

MAX_DURATION is the time, in minutes, the rebuild will wait to acquire the necessary locks before taking action. ABORT_AFTER_WAIT tells it what to do after that time period has passed. Setting it to NONE means SQL Server will just continue to wait while setting it to SELF will cause it to give up on the rebuild. If this rebuild absolutely must finish, this could be set to BLOCKERS which would kill the process or processes preventing it from completing. To see this in action, lets first create our old scenario without the WAIT_AT_LOW_PRIORITY option.

LowPriority1

Now let’s try the same thing except we will use the WAIT_AT_LOW_PRIORITY option with a MAX_DURATION of 1 and ABORT_AFTER_WAIT set to SELF. This means after a minute of waiting the rebuild will give up.

LockPriority2

As we can see, after a minute of waiting the rebuild gave up and we would not have been able to recreate our previous scenario.