Microsoft Azure

Resumable Online Index Rebuild Continued

Posted on by Melody Zacharias
15
Jan

Resumable Online Index Rebuild Continued.

As noted in the last blog, Resumable online indexes are designed to give you the ability to pause and resume index rebuilds.  This is important when you have a short window of opportunity to get an index rebuilt and it can not be rebuilt in its entirety during that time.  It is helpful if you suddenly find you need additional resources, to complete an index rebuild. See my previous blog on all the great reasons why this feature is important. 

Now I want to review the use of the feature.

First let’s look at our indexes, to see what we need to rebuild.

--0.0 CHECK the fragmentation OF the indexes
SELECT db = DB_NAME(s.database_id)
, [schedma_name] = sc.name
, [table_name] = o.name
, index_name = i.name
,s.index_type_desc
, s.partition_number  -- if the object is partitioned
, avg_fragmentation_pct = s.avg_fragmentation_in_percent
, s.page_count -- pages in object partition
FROM sys.indexes AS i
CROSS APPLY sys.dm_db_index_physical_stats (DB_ID(),i.object_id,i.index_id,NULL,NULL) AS s
INNER JOIN sys.objects AS o ON o.object_id = s.object_id
INNER JOIN sys.schemas AS sc ON o.SCHEMA_ID = sc.SCHEMA_ID
WHERE i.is_disabled = 0
AND o.object_id = OBJECT_ID('sales.orders');

This produced the following result set.

Fragmentation

You can see there is a real need for rebuilding these indexes.

Let’s use the Online rebuild option to do this

USE WideWorldImporters
GO
--1. Start Rebuild Index 
ALTER INDEX FK_Sales_Orders_SalespersonPersonID ON sales.orders
REBUILD WITH (ONLINE = ON, RESUMABLE=ON);

Once the index starts to rebuild I can pause it.

-- 2. Pause the index rebuild
ALTER INDEX FK_Sales_Orders_SalespersonPersonID
ON sales.orders PAUSE;

The pause index process only gives the standard Commands completed successfully. Message.  However,note that once you pause the index rebuild if you still have the query open where the index is running you will get this message below.  Although it is in red.  It is not an error and you should be expecting this.  It is just to let you know that a process has been interrupted. 

Once Paused, I can look at the index and see what the status us

--3. Find the index status
SELECT [NAME], SQL_text, state_desc
FROM sys.index_resumable_operations

Once paused the index can be resumed or aborted depending on what is needed.  The length of time that passes between the pause and resume or abort is not relevant.

Paused Index

Unfortunately, you do not see how far the index has gotten when you pause it.  Running your fragmentation query while the index rebuild is paused does not show a difference in the fragmentation.

No change noted when paused

Once paused you can resume the index at any time.

--4. Resume index rebuild
ALTER INDEX FK_Sales_Orders_PickedByPersonID ON sales.orders RESUME;

The result will simply show that the command completed successfully so you will want to rerun your index job to verify that the fragmentation has improved.

Verify improvement

You will also see an improvement in page count.

If you choose not to complete the rebuild you can simply abort the index rebuild while it is either running or paused.  This can be particularly useful if you have a rebuild running over multiple nightly windows and you realize you need to stop it to do other maintenance.

--Abort a resumable online index rebuild (when it is running or paused)
ALTER INDEX FK_Sales_Orders_PickedByPersonID on sales.orders  ABORT ;
X