The WAIT_AT_LOW_PRIORITY Option

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.

Breaking Radio Silence

Well it has been a few months since I have posted here but it’s time for me to share an update. I recently accepted a new position as a SQL Server DBA and I am excited to continue moving forward in my career. My previous role doing database support for a software vendor provided me with a great deal of experience working in both SQL Server and Oracle environments across many industries. In my new position I will get to experience taking ownership of an environment of my own and facing all the challenges that come with that responsibility. I believe this move will allow me to become a more well rounded SQL Server professional and I am incredibly excited for the opportunity.

Between buying a house, planning a wedding, and accepting my new position I have been quite busy lately and that has resulted in me being pretty quiet on here. I aim to be more active on this blog and more active in the community once the chaos settles down.