SQL Server 2017 Resumable Online Index Rebuilds

By:   |   Comments (1)   |   Related: > SQL Server 2017


Problem

Database performance optimization is always a key task for the DBA. Index maintenance plays a vital role in optimizing database performance. Sometimes in busy OLTP environments we have very limited maintenance windows and if an index is large we may not have enough time to rebuild the index. SQL Server 2017 provides a solution for this and in this tip we will see how we can use resumable index rebuilds.

Solution

SQL Server 2017 offers a new feature for maintaining indexes which we will cover in this tip.

Resumable Online Index Rebuild - Overview

SQL Server 2017 provides a key feature Resumable Online Index Rebuilds. In previous versions of SQL Server, we might need to cancel the index maintenance operation either due to running out of time in the maintenance period, blocking issues or maybe low disk space due to the transaction log filling up.  When we cancel the index rebuild, we need to start the entire operation again to rebuild the index.

Resumable Online Index Rebuilds provide the option to pause an index rebuild and provides an option of executing, suspending, resuming or aborting an online index operation.

Note: Currently SQL Server 2017 is supporting only row store indexes.

Use of Resumable Online Index Rebuild

We can use the new feature in the following scenarios:

  • We can resume an index rebuild operation after an index rebuild failure, such as after a database failover or after running out of disk space.
  • We can pause an ongoing index rebuild operation and resume it later. For example we may need to temporarily free up system resources to execute a high priority task or due to a short maintenance window. Instead of aborting the index rebuild process, we can pause the index rebuild operation and resume it later without losing prior progress.
  • We can rebuild large indexes without using a lot of log space and have a long-running transaction that blocks other maintenance activities. This helps log truncation and avoids out-of-log errors that are possible for long-running index rebuild operations.

The syntax for Resumable Online Index Rebuild is as below:

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
 }
[ ; ]

rebuild_index_option > ::= 
{
   ….
  | ONLINE = { ON [ ( <low_priority_lock_wait> )] | OFF }   
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [MINUTES]
  | PAUSE
  | ABORT
  | …
}

We can see the options below that can be used:

  • ONLINE: Resumable index rebuild supports only online index rebuild, so we have to specify ONLINE = ON.
  • RESUMABLE: If we want to use the resumable index rebuild functionality, we need to execute the online rebuild with RESUMABLE option. If we want to do a normal online index rebuild then either specify RESUMABLE=OFF or don't specify this option. RESUMABLE=OFF is the default value.
  • MAX_DURATION= [MINUTES]: This is also an interesting and useful option to specify the max duration. It specifies the number of minutes that the reusable online index operation will be executed, before being suspended. The time in minutes for this MAX_DURATION option, must be greater than 0 and less or equal to one week (7 x 24 x 60= 10080 minutes).
  • PAUSE: It pauses the resumable online index rebuild operation.
  • ABORT: It aborts a running or paused resumable online index rebuild operation.

Now lets see how to use this functionality.

To perform a test, I will be using the sample database WideWorldImporters and table [Sales].[InvoiceLines].

First let's check the clustered index on this table.

select
    o.name as table_name,
    i.name as index_name,
    p.index_id,
    au.type_desc,
    au.used_pages,
    p.rows
from
    sys.allocation_units as au
join
    sys.partitions as p on au.container_id = p.hobt_id
join
    sys.objects as o on o.object_id = p.object_id
left join
    sys.indexes as i on i.object_id = p.object_id
                        and i.index_id = p.index_id
where p.object_id = object_id('[Sales].[InvoiceLines]')
and i.index_id=1
SQL Server Index

Now we will initiate an online index rebuild using the resumable index option. To do this, open a query window and run the below query.

ALTER INDEX PK_Sales_InvoiceLines 
ON [Sales].[InvoiceLines] 
REBUILD
WITH (ONLINE = ON, RESUMABLE = ON);
GO
SQL Server Index

Note that we have to specify ONLINE = ON with RESUMABLE = ON, if we don't specify ONLINE=ON then it will throw the below error:

SQL Server Index

Once the index rebuild is running, open a new query window and run the below query:

ALTER INDEX PK_Sales_InvoiceLines ON [Sales].[InvoiceLines]  PAUSE
SQL Server Index pause

Once the above query completes, notice the first query window's output.

SQL Server Index pause output

This is not an error message, this is the expected output. We can see in the output that it kills the session under which the index rebuild is running. So if we are running the index maintenance using a SQL Agent job, it will kill the job under which the index rebuild is running.

SQL Server 2017 includes a new system view sys.index_resumable_operations to monitor and check the current execution status for resumable index rebuilds.

SELECT total_execution_time, percent_complete, name,state_desc,last_pause_time,page_count
FROM sys.index_resumable_operations;
SQL Server Index pause view

We can see that we have paused the online resumable index rebuild operation after 50.94% completion, the current status is PAUSED and last_paused_time is 2017-07-02 12:08:14.110.

We cannot drop the index while the index is in a PAUSED state, if we try we will get the below error.

drop index PK_Sales_InvoiceLines
on [Sales].[InvoiceLines]
SQL Server Index pause output

Suppose we have paused an online index rebuild operation due to a space issue as our transaction log has grown. The best part is that we can shrink the transaction log with the index rebuild status being PAUSED.

First let's check the transaction log usage while the index rebuild status is paused.

select
    used_log_space_in_bytes / 1024 / 1024  as used_log_space_MB,
    log_space_in_bytes_since_last_backup / 1024 / 1024 as log_space_MB_since_last_backup,
    used_log_space_in_percent
from sys.dm_db_log_space_usage;
go
SQL Server Index pause output

If we check the log_reuse_wait_desc status, we can see that a LOG_BACKUP is required.

SQL Server Index pause output

After a log backup is completed, we will be able to shrink the transaction log.

SQL Server Index pause output

We can see that now the status of the database for log_reuse_wait_desc is NOTHING which means we can shrink the transaction log.

SQL Server Index pause output

Resume the Online Index Rebuild Operation

To resume the Online Index operation that we paused earlier, we need to use the below query.

ALTER INDEX PK_Sales_InvoiceLines ON [Sales].[InvoiceLines]  RESUME
SQL Server Index pause output

If we try to run the index rebuild again, it will not do the rebuild process from the start, however it will resume the index rebuild operation.

SQL Server Index pause output

Once the rebuild is complete, we won't see any entries in the sys.index_resumable_operations.

SQL Server Index pause output

Abort the Online Index Rebuild Operation

If we want to abort the resumable index rebuild operation, we can do so by specifying the Abort option.

ALTER INDEX PK_Sales_InvoiceLines ON [Sales].[InvoiceLines]  Abort

With this we can see that the index operation will abort in the session where the index rebuild is running..

SQL Server Index pause output

Resumable Index with MAX_DURATION Option

We don't want our index maintenance operation to run during business hours. Currently we don't have an option to stop the index rebuild after a specific duration. In SQL Server 2017, we now have the option to specify the Max_Duration in terms of minutes to run for the resumable index rebuild.

Suppose I want my resumable index rebuild to run for maximum of 1 minute and then stop automatically, so my query will be.

ALTER INDEX [ix_ColdRoomTemperatures_Archive]
ON [Warehouse].[ColdRoomTemperatures_Archive]
REBUILD
WITH (ONLINE = ON, RESUMABLE = ON,MAX_DURATION =1 MINUTES);
GO

Now let's run the query and we can see it stops after 1 minute.

SQL Server Index pause output

We can see that query executed for a maximum of 1 minute and then automatically went to a PAUSED state.

SQL Server Index pause output

Similarly if want to resume the operation and want it to run for specific duration, we can specify Max_Duration as well.

ALTER INDEX PK_Sales_InvoiceLines ON [Sales].[InvoiceLines]  Resume WITH (MAX_DURATION =1 MINUTES)

Updated sys.dm_exec_requests DMV

DMV sys.dm_exec_requests was updated in SQL Server 2017 with an additional column is_resumable to identify Resumable Online Index Rebuild operations.

SQL Server Index pause output

Restriction for Resumable Index Rebuild

Some things to note:

  • It works only on row store indexes.
  • It doesn't work with the SORT_IN_TEMPDB option.
  • It doesn't work with TimeStamp columns.
  • It doesn't work with Computed Columns.
  • We cannot use it on Disabled Indexes.
  • The RESUMABLE INDEX statement cannot be used inside a user transaction.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Saturday, January 20, 2024 - 1:22:37 PM - Tara Chandra Back To Top (91868)
Hi there, I appreciate you for your work, as i have read microsoft doc for the same info and you have also mentioned that tempdb option is not available in 2017 version, and i am having 2017 enterprise version and i have successfully executed alter command to rebuild indexes wtih sort in tempdb option. since on that time i had not this info. i executed it executed successfully, till now i was rebuilding indexes through the same method today i come to know this, can test this.














get free sql tips
agree to terms