Resumable Indexes in SQL Server

What is Resumable Index?

When rebuilding indexes online, index rebuild process could be fail for reasons such as failover, disk failure, manual stop (PAUSE). In such cases, you must mark the index as RESUMABLE = ON in order for the rebuild process to resume after it fails.

Create a Resumable Index

You can create a resumable index with the following script.

Alter an Index With Resumable

You can alter the index as resumable with the following script.

If you do not add “ONLINE = ON” to the script, you will receive an error as follows.

Msg 11438, Level 15, State 1, Line 3

The RESUMABLE option cannot be set to ‘ON’ when the ONLINE option is set to ‘OFF’.

Pause Index Rebuild Process

Below is the code that pauses the index rebuild process.

Resume Paused or Failed Index Rebuild Process

Below is the query that you can resume a paused Index Rebuild process.

Suppose you forgot to start Index rebuild with maxdop. You can increase the parallelism level of the query by pausing the Index Rebuild operation and changing the maxdop level while resume.

Resumable Index Options

In the following query, we resume the Index with different options. For other options in the query, I recommend reading “How To Manage Online Index Rebuild and Partition Switch Processes With WAIT_AT_LOW_PRIORITY“.

Abort Index Rebuild Process

We can abort the index rebuild process which is pause or running, with the help of the following query.

Resumable Index Limitations

  • SORT_IN_TEMPDB = ON is not supported in RESUMABLE indexes.
  • We cannot rebuild disabled indexes as RESUMABLE.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *