Resumable Online Index Create in SQL Server 2019

By:   |   Comments   |   Related: > SQL Server 2019


In SQL Server 2017, Resumable Online Index Rebuilds were introduced that allow for pausing and resuming index rebuild operations. This feature is especially useful in busy OLTP environments where there are limited maintenance windows. We explored this feature in SQL Server 2017 Resumable Online Index Rebuilds and More on Resumable Online Index Rebuilds in SQL Server 2017.

Recently Microsoft launched the SQL Server 2019 preview at Ignite 2018. SQL Server 2019 contains many feature enhancements over earlier versions of SQL Server. In this tip, we will look at the enhancements for online index creation in SQL Server 2019.


Before we move further, you should go through the below tips to familiarize yourself with SQL Server 2019.

SQL Server 2019 provides an important feature Resumable Online Index Create. In SQL Server 2017, we can only perform a resumable online index rebuild. Suppose we have to create an index on a big table with millions of rows. It might take time to create an index on this table.  While the index creation is in progress, due to some performance issues or failover, we need to cancel the create index statement. It can be a big pain again as we have to start it again which will again take time and resources to start the index rebuild all over again.

In the recent preview version of SQL Server 2019, we can pause and resume the online index creation process similar to resumable online index rebuilds. We can cancel this process as well without affecting existing indexes. Previously we had to stop the index creation, which also takes a lot of time to roll back and then start from the scratch again.

This feature can be useful in the following scenarios occur:

  • Database failover
  • Disk space issues
  • System resource contention
  • Minimize transaction log growth

In order to use this feature, we need to:

  • Set database compatibility mode to SQL Server vNext (150 - SQL Server 2019)
  • Specify Resumable=ON for the online index create command


In this tip, we will use SQL Server Management Studio (SSMS) 18.0, which is a preview version which supports the SQL Server 2019 preview version.

Download SSMS 18.0 preview version 4

You can download SQL Server Management Studio 18.0 Preview 4 from this link: Download SQL Server Management Studio 18.0 (preview 4)

Once downloaded and installed, launch SSMS 18.0 as shown below.

SSMS 18.0 Preview 4 launch screen

Connect to SQL Server 2019 preview version (SQL Server vNext CTP 2.0 - 15.0.1000.34)

SQL Sever 2019 preview connect with SSMS 18.0

Let's explore Resumable Online Index Create with examples.

SQL Server Resumable Online Index Create Example

Let’s prepare the database and table to perform resumable online index create example.

For demonstration purposes, I am using the below sample database in my instance and you can see that the compatibility level is SQL Server vNext (150). If you are restoring a database from a previous version, you will need to change the compatibility level to 150.

compatibility level 150 in SQL Server 2019

In the below script, let's create a table and insert a large amount of dummy data into it with the help of this tip.

   Id int identity primary key,
   Name nvarchar(50),
   Address nvarchar(50)
Declare @Id int
Set @Id = 1
While @Id <= 10000000   --you can change the number of rows using this value
   Insert Into MSSQLTips values ('TestData - ' + CAST(@Id as nvarchar(10)),
              'TestCountry - ' + CAST(@Id as nvarchar(10)) + ' name')
   Print @Id
   Set @Id = @Id + 1

Now, we have the set up ready to create an index. Let's create an index on the ID column with the below script.

ON dbo.MSSQLTips(Name)

Note that we have specified RESUMABLE=ON in the statement. Execute this script now.

Resumable Online Index Create in SQL Server 2019

We can see in the error that we cannot set the RESUMABLE=ON without specifying Online=ON in create the index command.

So, let’s run the above command with Online=ON, RESUMABLE=ON as shown below.

Resumable Online Index Create with online=On in SQL Server 2019

Now let's create another index on this table. Then we will pause this index creation and observe the behavior of the index.

To do so, run the below query in session 1 in Management Studio.

Session 1: Create an index with Resumable=ON

ON dbo.MSSQLTips(Name,Address)

While the query is running in session 1, open a new query window and execute the below command.

Session 2: PAUSE - Create index with below alter index command with Pause clause


Once we execute the query in session 2, we get the below error message in session 1. This error message shows the create index is in a paused state.

Msg 1219, Level 16, State 1, Line 5
Your session has been disconnected because of a high priority DDL operation.
Msg 1219, Level 16, State 1, Line 5
Your session has been disconnected because of a high priority DDL operation.
Msg 596, Level 21, State 1, Line 4
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 4
A severe error occurred on the current command.  The results, if any, should be discarded.
Resumable Online Index pause output  in SQL Server 2019

However, session 2 shows the command completed successfully as shown below. This is because the create index was placed into a paused state successfully.

script to pause Resumable Online Index  in SQL Server 2019

Now let's view the index status using the system view sys.index_resumable_operations.  This view monitors and checks the current execution status for resumable indexes.

FROM sys.index_resumable_operations;

We can see that the index is in a paused state and the percent completed is around 45%. It also shows the page count that defines the total number of index pages allocated by the index build operation.

Check the progress of resumable index using sys.index_resumable_operations;

As we can see the index is in PAUSED state, so we need to RESUME the index build using ALTER INDEX with RESUME command. Let's resume the index and cancel it quickly to see what happens to the index.

Resume and Cancel the progress of resumable index

Now again monitor the index status. We can see the index is still in a PAUSED state, but it has moved further towards completion. We can see this in the change in percent_complete and page_count columns.

View the status of resumable index in SQL Server 2019

Now we will leave this index [NCI_MSSQLTips_1] in a PAUSED state and try to create another resumable index on the same object. This gives the below error that we cannot create another resumable online index on this object, because the previous index is in index rebuild state.

Msg 10637, level 16, State 3, Line 5
Cannot perform this operation on 'object' with ID 629577281 
as one or more indexes are currently in resumable index rebuild state.
create another resumable index

Now let’s RESUME the create index on [NCI_MSSQLTips_1]. We can see below that the query is in an executing state.

Resume the paused index sql server 2019

While the index resume is running, we can see the index information in the system view and it shows the status as RUNNING instead of PAUSED.

Running status of index after resume index

Note, we cannot create a resumable index in tempdb.  Let 's try creating the index with the SORT_IN_TEMPDB=ON option to see what happens.

ON dbo.MSSQLTips(Address)

We get the below error message.

Msg 11438, Level 15, State 2, Line 7
The SORT_IN_TEMPDB option cannot be set to 'ON’ 
when the RESUMABLE option is set to 'ON’.
Error if we use SORT_IN_TEMPDB option for resumable index.
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

get free sql tips
agree to terms