More on Resumable Online Index Rebuilds in SQL Server 2017

Problem

In a previous tip, Rajendra Gupta provided a thorough introduction to a new feature
in SQL Server 2017,
Resumable Online Index Rebuilds. This feature allows you to pause index rebuilds
for various reasons, including bumping up against the edge of your maintenance window,
forcing a failover, or installing emergency patches.

You can also pause an online index rebuild and resume with a different
MAXDOP setting, either to make it go faster, or to
stop it from consuming all of your CPU.

But what really happens while this operation is paused? What happens if you try
to rebuild an index that is already being rebuilt and is currently paused? What
happens if SQL Server crashes while a resumable index rebuild is processing?

Solution

These are scenarios not typically mentioned in blog posts, but they are easy
to test out.

You can open a new query window during that first minute and run queries against
the table, just to be sure that the operation is in fact happening online and that
user activity is not disrupted.

After a minute, youíll get a series of error messages, since the way
MAX_DURATION works (as well as issuing
PAUSE from a remote session) is that the session gets
killed. This is not the friendliest experience, Iíll admit, but it works.

The point is, what do you think happens while this operation is paused? Rajendra
already explained that you can use this opportunity to back up the log (or issue
a checkpoint) and even perform shrink operations, so clearly the log isnít
being used (at least not directly) in order to maintain the work thatís been
done so far. This data must be stored elsewhere (and it can therefore affect your
database size). But where?

(Yes, there are two index copies being built, since rebuilding the clustered
index also rebuilds all the non-clustered indexes.)

From those results, you can see that the rebuild work was paused after just a
little more than 50% of the work was done, and that the data is stored in ďhiddenĒ
indexes (they do not appear in sys.indexes). Note that these hidden
indexes are not captured by the aggregate information returned by sp_spaceused,
so that might not be the best tool to use if youíre trying to determine where
any extra space is coming from. The point is that this resumable rebuild operation
does take up space in your database (and note that SORT_IN_TEMPDB is
not a viable option here Ė Iíll bet you can guess why).

As an aside, you can use this information to determine how close you are to being
finished and, if youíve kept the amount of time each resumable operation has
taken, how much longer it will take. You can even use this information over time
to build trends and predict durations for future rebuild operations as your database
grows.

What happens if I try to rebuild an index that is already paused?

As you might expect, if you try to rebuild an index that is already part of a
resumable operation, you will get an error message similar to one you would get
from an index that is actively rebuilding. The text is slightly different (I think
there is a token issue somewhere in current builds that will hopefully be fixed
before RTM):

Msg 10637, Level 16, State 3, Line 28

Cannot perform this operation on 'object'
with ID 1090102924 as one or more indexes are currently in resumable index rebuild
state. Please refer to sys.index_resumable_operations for more details.

The moral of the story is that if you have any tools, scripts, or 3rd party applications
that perform defrag / rebuild operations for you, theyíll need to additionally
check sys.index_resumable_operations to make sure
that there isnít an operation currently suspended. They may already have error
handling in place, but it may be coded to a different error number or based on parsing
different message text. What it may be looking for is:

Msg 1912, Level 16, State 1, Line 1

Could not proceed with index DDL operation
on table 'MovementCopy' because it conflicts with another concurrent
operation that is already in progress on the object. The concurrent operation
could be an online index operation on the same object or another concurrent
operation that moves index pages like DBCC SHRINKFILE.

Letís finish up our paused operation so we can start a new one in the next
section:

ALTER INDEX WideClustered ON Fact.MovementCopy RESUME;

What happens if SQL Server crashes during a resumable operation?

I donít know what I expected to happen here, and I donít know that
itís reasonable to have expected this to be super graceful, but this is also
easy to test. First, make sure you are on an unimportant, non-production instance
of SQL Server. I canít stress this enough.

Now, quickly open a second window, and shut the server down (again, verify youíre
not on production!):

SHUTDOWN WITH NOWAIT;

Of course, you canít check on anything until you restart the server, so
go into Control Panel / Services, find your instance, and choose Start. Then check
the resumable operations view:

SELECT * FROM sys.index_resumable_operations;

Sure enough, I get a row back, and the operation is resumable, even after a crash.
I can finish the operation as follows:

ALTER INDEX WideClustered ON Fact.MovementCopy RESUME;

Next Steps

This new feature in SQL Server 2017 helps out in a variety of important use cases
for rebuilding indexes. Go ahead and
download a pre-release evaluation, and start getting a taste of the future of
index management! In the meantime, some other resources:

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

What happens if I try to rebuild an index that is already paused?

There will be an alert saying: An existing resumable operation with the same options was identified for the same index on .... The existing operation will be resumed instead.

* Nice post.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.