SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

 


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

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...