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.
Pingback: Resumable Online Index Rebuild – Curated SQL
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?
LikeLike
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.
LikeLiked by 1 person