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
 

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.

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
 

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

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

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.

Filed under: Azure, Azure SQL DB, DMVs Tagged: Azure, Azure SQL DB, DTU, Indexes, TSQL