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:

Traditional Online Index Rebuild

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.

Traditional Online Index Rebuild-Blocking

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:

2014 Online Index Rebuild-Blocking

And here’s what the blocking looked like:

LCK_M_SCH_M_LOW_PRIORITY

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:

abort_after_wait_self

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.

Learn More

Previous Post
We’re Hiring a Salesperson
Next Post
The Basics of Oracle Statistics

9 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}