Azure SQL Database Resumable Online Index Rebuild

If you know me by now I like rebuilding indexes and that is no different in Azure. Now we have the ability to resume a paused rebuilding operation rather than cancelling it (Feature currently in public preview). I like this because I have the flexibility to pause it if I feel that it is taking up too much DTU (Database Transaction Unit) usage hence I can free up resources for other operations.

 ALTER INDEX [PK_Audit] on [dbo].[Audit] REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON) ;

Checking the main system view for this operation:

SELECT * FROM  sys.index_resumable_operations
 

IndexBuild

Note the percent complete field is 1.06%. See the spikes in DTU consumption? This correlates to when I start a rebuild. I want to pause it.

FirstSpike

In a different query window, I run:

 --Pause and relax
ALTER INDEX [PK_Audit] on [dbo].[Audit] PAUSE ;
 

I recheck the system view.

SELECT * FROM  sys.index_resumable_operations
 

PauseMe
Clearly I am in a paused state with 5.48% percent of the operation complete. In the original query window where I started the index rebuild, I receive:

Msg 1219, Level 16, State 1, Line 4 Your session has been disconnected because of a high priority DDL operation. Msg 0, Level 20, State 0, Line 3

A severe error occurred on the current command.  The results, if any, should be discarded.

DTUs when in a paused state, it drops back down to pretty much 0 DTU consumption (red arrow below).

redarrow

So I want to resume it now.

--Start it up
ALTER INDEX [PK_Audit] on [dbo].[Audit] RESUME

SELECT * FROM  sys.index_resumable_operations

Once resumed notice the paused timestamp gets updated which is quite nice and couple minutes later I have further progressed with the rebuild to over 10%.

10percent

Then you can let it finish. I decided to ABORT the whole thing and call it a day.

--Abort

ALTER INDEX [PK_Audit] on [dbo].[Audit] ABORT

This is what my DTU graph looked like after lots of pausing and resuming, the red circles are when I paused the operations.

DTUFUN

 

3 thoughts on “Azure SQL Database Resumable Online Index Rebuild

  1. Pingback: Resumable Online Index Rebuild – Curated SQL

  2. This is a cool feature, thank you for sharing.

    Do you know if the rebuild is occurring on a copy? What I really am wondering is if you pause the rebuild, will SQL then use an original, unmodified version of the index, or will it begin using the partially rebuilt one?

    Like

  3. Hey Bert. Top question. I couldn’t find any evidence of a copy being produced from looking at system tables. regarding where it picks up from…. I just assumed it picks up where it left it from the pause , but I am not totally sure.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s