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

 

One thought on “Azure SQL Database Resumable Online Index Rebuild

  1. Pingback: Resumable Online Index Rebuild – Curated SQL

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