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.
--Warning: this fillfactor is terrible for performance
--Don't do this in production!
ALTER INDEX [IX_TransactionHistory_ProductID]
REBUILD with (fillfactor=1);
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:
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.)
exec sp_WhoIsActive @get_locks=1;
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:
<Lock request_mode="S" request_status="GRANT" request_count="1" />
<Object name="TransactionHistory" schema_name="Production">
<Lock resource_type="OBJECT" request_mode="IS" request_status="WAIT" request_count="1" />
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.