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:
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:
1 2 3 4 5 |
ALTER INDEX PK_Votes ON dbo.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.
Learn More
- 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
13 Comments. Leave new
Very interesting, thanks for the article!
Really nice feature. Thanks for sharing with us.
Good feature, ABORT_AFTER_WAIT = BLOCKERS is very exciting 🙂
Is there any option available to kill only current statement on which table the rebuild index is running and waiting for locks,
If we have several tables in a loop for rebuilding indexes and we just want to skip on that particular table and proceed further on other tables.
Yep, make sure to read the post.
Can you link the post that explains how to continue on to the next table or partition? Because I have the same problem, it’ll abort, but I’d like to continue processing more records.
This feature is for 2014 , do we have anything for 2012 where we kill blockers while doing online rebuild
No.
Just read this today…nice to see that the current OLA scripts also support these options 🙂
WAIT_AT_LOW_PRIORITY and ABORT_AFTER_WAIT are implemented in Ola Hallengren scripts ?
For support with Ola’s scripts, check https://ola.hallengren.com.
Is the WAIT_AT_LOW_PRIORITY only for online index rebuilds? How do we achieve the same to mitigate blocking due to LCK_M_SCH_M if we are not on the Enterprise edition of SQL Server?
How does this work using read committed snapshot isolation? ie if we do not use the WAIT_AT_LOW_PRIORITY would the 3rd query still get blocked if you exclude the nolock hint?