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:
- An online index rebuild starts against a large index
- 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
- 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:
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:
- 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:
ALTER INDEX PK_Votes
REBUILD WITH (ONLINE = ON (
WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF ))
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.
- Research more on your own by reading Kendra’s “Rebuild or Reorganize?” blog post
- Get into the controversy of index maintenance by watching Brent’s “Index Maintenance Doesn’t Matter” video
- Learn the foundations of SQL Server Database Maintenance online with Jes and Kendra in the Database Maintenance Starter Kit course
- Learn advanced database maintenance in our in-person Senior DBA class with sessions in Denver and Chicago in 2015