This question came up in the pre-show chat for our weekly webcast: when you rebuild a nonclustered index offline, does it impact only the nonclustered index? Or does it impact the entire table?
Showing is more fun than telling! Let’s take a look.
First I restore AdventureWorks to slow storage
Sometimes slower storage is handy. To demo any questions like this, I like to restore a copy of AdventureWorks2012 onto a handy external Seagate drive. I love having SSDs in my workstation, but when it comes to wanting to test out things like blocking on a reasonably small amount of data, it pays to have some poky storage around.
Now I run a super slow nonclustered index rebuild
Once AdventureWorks is restored, I set up an offline index rebuild in one session window— and to make it take longer I set up my index rebuild command in a BAD way. I set fillfactor to 1 percent, which means SQL Server is going to explode this table and leave 99% of each page empty.
|
1 2 3 4 5 6 |
--Warning: this fillfactor is terrible for performance --Don't do this in production! ALTER INDEX [IX_TransactionHistory_ProductID] on [Production].[TransactionHistory] REBUILD with (fillfactor=1); GO |
In another session, I query the table’s clustered index
While the index rebuild is running, I open a second window and run a SELECT query. This select query is designed to specifically read from the clustered index of the table and not use the nonclustered index at all:
|
1 2 3 |
SELECT Quantity FROM [Production].[TransactionHistory]; GO |
Now I check out the blocking
Now, from a third session I check and see– is my SELECT from the clustered index blocked by the rebuild on the nonclustered index? To get all the details, I run Adam Machanic’s sp_whoisactive with a parameter to get information on locking. (This can be a bit expensive, so only use this option when you need it.)
|
1 2 |
exec sp_WhoIsActive @get_locks=1; GO |
Sure enough, my SELECT query is blocked by my index rebuild.
Clicking on the ‘locks’ XML column for my blocked SELECT statement, I can see more detail:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<Database name="AdventureWorks2012"> <Locks< <Lock request_mode="S" request_status="GRANT" request_count="1" /> </Locks> <Objects> <Object name="TransactionHistory" schema_name="Production"> <Locks> <Lock resource_type="OBJECT" request_mode="IS" request_status="WAIT" request_count="1" /> </Locks> </Object> </Objects> </Database> |
This confirms that the read has requested an intent shared lock on the Production.TransactionHistory object– but it can’t get the lock.
Finding: offline rebuilds of a nonclustered index prevent querying the clustered index
If you need to rebuild indexes offline, this means that you can’t minimize the impact by only rebuilding the nonclustered index– rebuilding them places locks on the object itself. We saw this by seeing that queries who just wanted an intent shared lock on the table were blocked.
If our indexes were large and tables needed to be available constantly, this could be a huge problem!
Enterprise Edition Doesn’t Fix Everything
With SQL Server Enterprise Edition, you can specify that you’d like to do an “ONLINE” rebuild. In this case things are a little different– users can access the table during most of the index rebuild. However, at the end of the rebuild SQL Server still needs an exclusive “Schema Modification” lock (SCH-M) to finish the operation– and in highly concurrent systems, that can still be an issue.
If you’d like to reproduce that issue at home to demo for yourself, it’s easy! Just start up the SELECT statement first and change the rebuild to use the “ONLINE” option.
But Wait, There’s More
This logic also applies to creating indexes– if you’ve got Enterprise Edition, you want to remember to always create your nonclustered indexes with the “ONLINE” option if you need to avoid blocking on the table. Even then, that pesky SCH-M lock can be a killer on very busy systems.
17 Comments. Leave new
I love your method of using a slow drive for this type of stuff. So simple and elegant it’s genius.
Nice post. Thanks Kendra.
Kendra,
Thanks for the post. One thing I noticed, if you do a DROP/CREATE instead of a REBUILD OFFLINE, you can run your SELECT.
I tried this on a copy of a PRODUCTION database with 2M+ rows. REBUILD OFFLINE would take 3 minutes on one of the indexes, and my SELECT statement would hang until it was complete.
When I DROP/CREATE the index, I’m able to run my query immediately.
The application in question is in the healthcare space. Maintenance runs nightly/weekly, with index rebuilds only when fragmentation hits a threshold. We have 2 users online during maintenance vs 100’s during normal hours, so load is very low. Ultimately *we’re fine with the index being offline.* *I’m not fine with the DB being offline,* which is really the behavior you demonstrate…
So, what’s the downside? Why are index rebuilds so intrusive?
Jeremy – because you’re building a new copy of the object. If your server isn’t fast enough to do that, you’ll have performance degradation.
Understood, but we have the same problem on REBUILD and not being able to query a table because of a lock to boot.
* Do you have any understanding of the technical reason behind the lock on the table?
* Are there any other issues?
* Is a REBUILD somehow more efficient than a DROP/CREATE?
* If not, should it be a best practice for any non SQL Enterprise install?
Jeremy – unfortunately, troubleshooting blocks is kinda beyond what I can do in a blog post comment.
Thanks Brent, I appreciate the response, but I’m not asking you to troubleshoot blocks.
Kendra’s conclusion was:
An offline REBUILD on an index in SQL Standard will block a SELECT query.
I found:
A DROP/CREATE of an index in SQL Standard will not block the same SELECT query.
The Question:
Why wouldn’t you always do the DROP/CREATE if you aren’t using SQL Enterprise?
I have a better question: why do either one?
If you’re a fragmentation zombie, just reorg when you hit 40%, and update stats. Update stats being the important part.
Without looking at your test setup, it’s tough to say why. Perhaps you got lucky and none of the index pages were deallocated, and remained in memory when you did a drop and create? Maybe your table is really small? This is why Brent directed you to a more robust Q&A forum: you didn’t provide enough details for a good answer.
May be you can also consider sort_in_tempdb if you have enough storage on the disk and your tempdb resides on a different disk which will improve the rebuild performance. Drop/create process puts a S lock thats why you can select from the table VS rebuilding the index which acquirs the SCH-M lock.
Errr, no, dropping an index will change schema. Try this:
* Start a long-running select query
* Drop an index on that table
* Start another long-running select query
And run sp_WhoIsActive. You’ll see that while #1 is running, #2 waits for a schema mod lock, and will block #3 (even if #3 uses a nolock hint.)
Thats correct. Conflict between Sch-m and Sch-s. Also long running query with nolock can block index rebuild online or offline.
In your other blog https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/, you wrote:
“In other words, if you set a 90% fillfactor when you rebuild a clustered index, SQL Server will try to leave 10% of each leaf page empty.”
Now you say ” I set fillfactor to 1 percent, which means SQL Server is going to explode this table and leave 99% of each page empty.”
Which is correct? Thank you
Hey Generos1, the 1% was done as a test to perform large fragmentation of the index and to cause a slow rebuild. This was intentional I believe and only a one off exercise to show the impact of trying to query the table and index during a rebuild index operation. Stick to the 90% for normal use.
I’m concerned with the 2017 instance I inherited. It’s standard edition so online rebuilds are just not an option. Index maintenance has never been performed so fragmentation is through the roof. Business requires the data to be available pretty much 24/7 so what would you recommend? I know reorgs will not be enough but I’m not sure I have a choice.
Eric – unfortunately, personalized system advice is a little beyond what I can do in the comments.
I totally understand. I’m working to see what it would take to get us up to Enterprise possibly. I just wondered if there were options to fix these indexes without having to take them offline.
Maybe a blue green deployment were you do index maintenance on the passive database would work. Doubles your storage/log consumption though.