Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

More on Resumable Online Index Rebuilds in SQL Server 2017


By:   |   Read Comments (1)   |   Related Tips: More > SQL Server 2017

FREE Webcast > 5 Easy SQL Server Query Performance Boosters


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.

First, letís take our trusty WideWorldImportersDW database, and blow up the biggest fact table by 100x, so SQL Server has something to chew on:

SELECT mk = [Movement Key] + 0, [Date Key], [Stock Item Key], [Customer Key],
  [Supplier Key], [Transaction Type Key], [WWI Stock Item Transaction ID],
  [WWI Invoice ID], [WWI Purchase Order ID], [Quantity], [Lineage Key]
INTO Fact.MovementCopy FROM Fact.Movement;
GO SET NOCOUNT ON;
GO INSERT Fact.MovementCopy SELECT * FROM Fact.Movement;
GO 99

The system procedure sp_spaceused shows that this table is a 1.33 GB heap. We can add a couple of indexes to it:

CREATE CLUSTERED INDEX WideClustered ON Fact.MovementCopy
  ( mk, Quantity, [Supplier Key], [Customer Key], [Date Key] DESC )
  WITH (ONLINE = ON); CREATE NONCLUSTERED INDEX SkinnyNCI ON Fact.MovementCopy
  ( [Date Key], Quantity ) INCLUDE ([Lineage Key])
  WITH (ONLINE = ON);

And now it is more substantial (weíve doubled it to about ~2.62 GB). We can see how those are broken down in terms of pages per index as follows:

SELECT index_id, pages = COUNT(*) 
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'Fact.MovementCopy'), NULL, NULL, 'LIMITED')
GROUP BY index_id;

Your results may vary slightly, but I get:

SQL Server Index Page Count

Where is data stored during a resumable rebuild?

Letís start a rebuild of the clustered index, make it resumable, and set the max duration to a minute, because surely the operation wonít finish in that time:

ALTER INDEX WideClustered ON Fact.MovementCopy
  REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1 MINUTES);

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?

Well, letís run our query against the page allocations function again:

SELECT index_id, pages = COUNT(*) 
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'Fact.MovementCopy'), NULL, NULL, 'LIMITED')
GROUP BY index_id;

This time, I get:

Results from the SQL Server page allocations function

(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.

Next, in one window, start this operation:

ALTER INDEX WideClustered ON Fact.MovementCopy
  REBUILD WITH (ONLINE = ON, RESUMABLE = ON);

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:



Last Update:



next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips
Related 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    Notify for updates 


Send me SQL tips:

    



Monday, February 12, 2018 - 7:17:31 PM - Ronen Ariely Back To Top

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

This was fixed and executing a REBUILD will act as exeuting RESUM

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.

 


Learn more about SQL Server tools