Maximize Resumable Indexes

Resumable Indexes were a new feature introduced with SQL 2017. At first glance it didn't make sense why I would want to modify our maintenance plans to include resumable indexes because everything was running fine. This was one of those instances of we don't need resumable index rebuilds until we needed them.

We are running Microsoft Dynamics 4.0 that was released in 2007. This likely isn't the oldest software that you have seen being band-aided to stay in production, but it did not like running on SQL 2017. We had recently upgraded our database from 2008 to 2017 (that is an adventure for another day) and performance was lagging. Dynamics was making poor choices for query plans and I found that we needed to keep our index fragmentation much tighter than we previously had. Any fragmentation above 5% could potentially cause issues the query plan that Dynamics chose. Dynamics seemed to be choosing the query plans independently of SQL query engine to use in its overuse of cursors.

With a total database nearing 4TB of primarily compressed tables, tables approaching 1 billion rows, and tables with more data in indexes than data in the table; there just wasn't time in the day to rebuild all the indexes.

This is where the resumable index rebuilds came into save us. Resumable index rebuilds do just what they sound like, allow you to start a rebuild and pause it for whatever reason and then continue the rebuild later. You do not lose the time spent rebuilding indexes if you need to kill the rebuild process because of blocking against your production workload! When you combine this with the WAIT_AT_LOW_PRIORITY option you can run the rebuild and not babysit for interruptions to production!

Here is the basic code for rebuilding the index with a auto kill feature so you don't bring down production.

--Author:      Todd Palecek 
--Description: Rebuild indexes with a resumable and auto-kill method

--This will start the index rebuild and run for up to 35 minutes. If the rebuild is blocking it will
--only block for 1 minute before killing itself. 
ALTER INDEX [I_177DIMIDIDX] ON [dbo].[INVENTTRANS]
REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF ))
,RESUMABLE = ON,MAX_DURATION =35 MINUTES, maxdop=8);
GO

Now if your rebuild is causing blocking and dies you will need to restart it. This frequently happens when SQL is switching in the rebuild index for me.

--resume the rebuild for another x minutes and then pause
ALTER INDEX I_366MKCCUSTSALESSTATUS ON [dbo].SALESTABLE  Resume WITH (MAX_DURATION =1 MINUTES)
GO

A nice benefit of resumable indexes that the percent complete will show up in sp_whoisactive. You can also monitor progress here.

--monitor percent complete and duration
SELECT total_execution_time, percent_complete, name,state_desc
,last_pause_time,page_count
FROM sys.index_resumable_operations;