SQL Server Resumable Index: Is it good for you?

SQL 2017 introduced the ability to pause and resume index rebuild operations during database maintenance. This feature offers more flexibility to database administrators as it allows them to choose between offline and online reindexing along with pausing and resuming the index rebuild whenever needed.

What is Resumable Index in SQL Server?

Before the release of the resumable index, database administrators could execute index rebuild offline and online.

Offline offers faster execution, as the table is locked for any read or write operation, and the new index is built from the old index. During this process, no read or write operation is permitted. When the operation is done, the table lock is released, and read and write operations are permitted again. The Offline option is naturally faster.

Online keeps the table open for read and write operations. There is another copy of the index made, and all index rebuild operations are in that copy. All new rows operations are written to both indexes. When the rebuild is completed, the switch is done, and the new index copy gets in use. The Online rebuild allows for rebuild operations while the database is online. The downtime is minimal.

Note that the resumable index feature is available only in the SQL Server Enterprise edition and the free Developer edition. If you have this option on the table, you can play around with it, do a simple test, and see if this feature is useful in your case.

The Microsoft documentation states the following aspects for your considerations:

• You can manage, plan, and extend index maintenance windows. You can pause and restart the operations of index creation or rebuilding when you need to fit your maintenance windows.
• You can recover from the index create or rebuild failures (such as database failovers or running out of disk space).
• Pay attention that when an index operation is paused, both the original index and the newly-created one will require disk space. You will need to update them during the DML operations.
• You can enable truncation of transaction logs during the index creating or rebuilding operations.
• Note that the SORT_IN_TEMPDB=ON option is not supported

Generation Test Data

Let’s test the resumable index rebuild. I will use a container image running the SQL 2019 Server Developer edition. Also, I will create a small table with just a couple of columns, and insert about a million rows into that table. You can make the table grow bigger with more rows.

As I am using a Linux machine and cannot install SQL Server Management Studio, I’ll use the Azure Data Studio client to connect to my SQL Server. Have a look at the screenshot of my SQL Server properties:

We’ll create a sample database, a table, and an index with the below T-SQL scripts. You can execute them flawlessly with SSMS or dbForge Studio for SQL Server:

-- Create a new database called 'DatabaseName' 
-- Connect to the 'master' database to run this snippet 
USE master 
GO 
-- Create the new database if it does not exist already 
IF NOT EXISTS ( 
SELECT [name] 
FROM sys.databases 
WHERE [name] = N'dbatools' 
) 
CREATE DATABASE dbatools 
GO
Use dbatools 

-- Create a new table called '[TableName]' in schema '[dbo]' 
-- Drop the table if it already exists 
IF OBJECT_ID('[dbo].[TabletoIndex]', 'U') IS NOT NULL 
DROP TABLE [dbo].[TabletoIndex] 
GO 
-- Create the table in the specified schema 
CREATE TABLE [dbo].[TabletoIndex] 
( 
[Id] INT NOT NULL PRIMARY KEY, -- Primary Key column 
[ColumnName1] NVARCHAR(50) NOT NULL 
-- Specify more columns here 
); 
GO

To populate the table with random data, execute the below script:

--populate the table 
SET NOCOUNT ON 
Declare @Id int 
Set @Id = 1 
While @Id <= 1000000 
Begin 
Insert Into TabletoIndex values (@Id, 'Name - ' + CAST(@Id as nvarchar(10))) Set @Id = @Id + 1 
End 
SELECT count(*) from TabletoIndex

How to Rebuild Resumable Index

With a populated table ready, we can proceed to the resumable index. Let’s start with creating that index:

-- Create a nonclustered index with or without a unique constraint -- Or create a clustered index on table '[TableName]' in schema '[dbo]' in database '[DatabaseName]' 
CREATE UNIQUE INDEX IX_ID_Name ON [dbo].[TabletoIndex] (ID desc, [ColumnName1] DESC) WITH (SORT_IN_TEMPDB = OFF, RESUMABLE=ON, ONLINE = ON, MAX_DURATION=1) GO

Notice new options/ parameters in the above command. RESUMABLE=ON means that we want to have a resumable index operation. Max_Duration is the value is in minutes defining for how long we want indexing to run.

While the above command is running, open another session and execute the below T-SQL command to PAUSE the ongoing rebuild activity:

--Rebuild WITH RESUMABLE functionality 
ALTER INDEX IX_ID_Name ON [dbo].[TabletoIndex] PAUSE 
GO 

If the PAUSE command is successful, we pause the current indexing operation started about a minute ago. However, when you go back to the previous session for the rebuild command with resumable=ON, it returns an ugly error. Ugh. But yes, that is the expected behavior.

With this resumable index rebuild, SQL Server also introduced a new DMV sys.index_resumable_operations to check on paused operations. Let’s try to look into this DMV:

The DMV result query returns my index rebuild command, the percentage completed is a great thing, and more. When all your index rebuild operations are done, DMV returns empty:

Pretty neat, huh?

But what if you change your mind about the table? What if there was a change in requirements, and you need to make changes to the database design? Let’s try to drop the table:

It will give another ugly, lengthy error message:

Msg 10637, Level 16, State 1, Line 1
Cannot perform this operation on ‘object’ with ID 581577110 as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.
Total execution time: 00:00:00.018

From here, you will realize that you have no choice but to completely ABORT the operation or RESUME and let the rebuild complete.

See the T-SQL command to resume or abort the operation. Then you can drop the table successfully:

ALTER INDEX IX_ID_Name ON [dbo].[TabletoIndex] RESUME 
ALTER INDEX IX_ID_Name ON [dbo].[TabletoIndex] ABORT

The same error will also occur if you need to do other operations like totally drop the index or kill the current session.

But you ask yourself, is the resumable option in the first place? The answer is NO. For SQL 2019, all index creation is with RESUMABLE=ON by default. It’s because of these 2 scope statements:

ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE=WHEN_SUPPORTED ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE=WHEN_SUPPORTED 

Summary

The impact of using the resumable option on performance is no different than using the normal reindex operation. SQL Server just gives you more control over your database maintenance operations.

As for your periodic table index rebuild requirements, the best practice is still running index operations offline, or at least during off-peak hours to ensure minimal business impact.