Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M).

Here’s one way this can become a big problem:

1. An online index rebuild starts against a large index
2. A modification query runs before the index rebuild query. At the end of the index rebuild, it tries to get a schema modification lock. It can’t, because the modification query is still running
3. The index rebuild’s super-high-level lock request then causes a massive pile-up behind it– blocking even queries that want to do dirty reads*

*I’m not saying dirty reads are a good thing, just pointing out that even NOLOCK can be blocked

Here’s a screenshot of a quick repro of that scenario:

And here’s what the blocking looks like in Adam Machanic‘s free tool, sp_WhoIsActive, if those are three queries are left running for a bit. It can stay ugly for a long time.

Session 56’s need for a schema modification lock can cause a big backup!

Let’s Test WAIT_AT_LOW_PRIORITY and ABORT_AFTER_WAIT?

SQL Server 2014 introduced some new options for index rebuilds:

• WAIT_AT_LOW_PRIORITY
• MAX_DURATION (in minutes)
• ABORT_AFTER_WAIT (Kill yourself / Kill who’s blocking you / Kill others)

This sounds pretty good, but how exactly does it impact the scenario above? Does “WAIT_AT_LOW_PRIORITY” really mean that it won’t start that nasty blocking chain? Or is it just that I can use “MAX_DURATION” to control the duration of the pain in this case?

Let’s take a look. Here’s the modified command I’m testing:

Here’s the commands I ran this time:

And here’s what the blocking looked like:

LCK_M_SCH_M_LOW_PRIORITY lock wait type saves the day

One thing to notice about the screenshot of the blocking: there’s no third query being blocked. There’s no pileup! “LOW_PRIORITY” really does mean low priority – the “NOLOCK” query is able to get a shared schema lock and move forward.

What Happened with ABORT_AFTER_WAIT?

As instructed, my online index rebuild called it quits. Here’s the error message it threw when it gave up:

I Like It!

Blocking chains during maintenance window can be a huge pain– especially if you’ve paid a high licensing dollar to get online index operations. Having more options is definitely a plus.