Resuming Index Operations With Different Options

Starting with SQL Server 2017, Microsoft introduced the ability to pause and then resume index rebuilds. This was a great feature and with the release of SQL Server 2019, we now have the ability to do the same functionality on the actual create index process. The ability to pause and then resume index build processes is invaluable to most organizations. Previously, most DBAs had to carefully orchestrate index operations around daily activities, work loads, and reporting needs. These newer features help to elevate a lot of these scheduling issues that DBAs routinely encounter.

Additionaly, with the release in SQL Server 2017, a new system view, sys.index_resumable_operations was also introduced. This system view monitors and checks the execution status of a resumable index rebuild or creation. You can use this view to see which table might have a resumable operation currently pending.

During my colleague Monica Rathbun’s (B|T) precon at PASS Summit 2019, I got into a conversation with Anders Pedersen (B|T) about these features. He had asked the question ; when resuming an index create or rebuild operation, could you specifically change the original process options which were set? In other words, change the MAXDOP or duration from the original command.

The answer is, Yes. You can in fact change those options when the RESUME command is issued.

Let’s take a look.

Resuming an Index Create or Rebuild

Documentation on ALTER INDEX provides which options we can set when resuming a rebuild or creation operation:

<resumable_index_option> ::=
{
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait>
}

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

This means that we can change the MAXDOP, MAX_DURATION, and WAIT_AT_LOW_PRIORITY.

Using WideWorldImporters, I issued a resumable CREATE INDEX command with a MAXDOP = 1

USE WideWorldImporters
GO
CREATE INDEX ix_fact_sale_profit ON fact.sale (profit) WITH (ONLINE=ON,RESUMABLE=ON,MAXDOP=1)
GO
-- Executed in another session
ALTER INDEX is_fact_sale_profit on Fact.SALE PAUSE

Checking our new system view, sys.index_resumable_operations we can now see that the process has been paused.

We can see that the process is a CREATE index along with the actual SQL syntax that was provided to the instance.

Using Live Query Statistics we can also see the execution plan that depicts the MAXDOP = 1

We can then issue a RESUME command and change our options.

ALTER INDEX ix_fact_sale_profit on Fact.Sale RESUME WITH (MAXDOP=2,MAX_DURATION=1)

Using Live Query Statistics again, we can now see that the MAXDOP=2 along with the parallelism operator.

In this case, the max_duration was set to 1 minutes. The create process did not take longer than a minute, however, if it had the process would have paused automatically. Once it has been paused, the rebuild/create process would have to manually resumed.

Durability

One additional thing to note is that this resumable process is durable and will persist from a restart of the SQL Server instance. The metadata about the index create or rebuild will remain ready to be resumed whenever ready. This way you will not have to restart the entire process over again if the SQL instance is forced to reboot.

Furthermore, because the data is contained within the user database itself, if the database is involved in an availability group, you could failover to your secondary replica and re-issue the RESUME command. Assuming the database is online and functional, the index operation will resume accordingly.

Summary

Microsoft continues to evolve the SQL Server platform with every turn of the corner. Introducing resumable index rebuild and creation operations will provide a great benefit for database administrators. Scheduling index maintenance can now be easily scheduled to match their production workloads to ensure their indexes are rebuild and created in an appropriate manner.

Furthermore, for you Azure SQL DB users, these features are already present in your database in Azure. Take advantage of them if you have a highly concurrent workload to ensure minimal disruption to your production environment.