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