Resumable Online Index Create in SQL Server 2019

By: Rajendra Gupta   |   Updated: 2018-12-21   |   Comments   |   Related: > SQL Server 2019

Problem

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.

Solution

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

• What's New in the First Public CTP of SQL Server 2019
• Discovering New System Objects and Functions in SQL Server 2019
• SQL Server 2019 Tips

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

Pre-requisites

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.

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.

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

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.

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.

CREATE Table MSSQLTips
(
   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
Begin 
   Insert Into MSSQLTips values ('TestData - ' + CAST(@Id as nvarchar(10)),
              'TestCountry - ' + CAST(@Id as nvarchar(10)) + ' name')
   Print @Id
   Set @Id = @Id + 1
End

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

USE MSSQLTipsDemo
GO
CREATE NONCLUSTERED INDEX CI_MSSQLTips
ON dbo.MSSQLTips(Name)
WITH (RESUMABLE = ON ) ;

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

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.

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

USE MSSQLTipsDemo
GO
CREATE NONCLUSTERED INDEX NCI_MSSQLTips_1
ON dbo.MSSQLTips(Name,Address)
WITH (Online = ON,RESUMABLE = ON);

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

ALTER INDEX [NCI_MSSQLTips_1] ON [dbo].[MSSQLTips] 
PAUSE;
GO

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.

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

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.

SELECT 
   name, 
   percent_complete,
   state_desc,
   last_pause_time,
   page_count
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.

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.

ALTER INDEX [NCI_MSSQLTips_1] ON [dbo].[MSSQLTips] 
RESUME;
GO

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.

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.

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

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.

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.

USE MSSQLTipsDemo
GO
CREATE NONCLUSTERED INDEX NCI_MSSQLTips_2
ON dbo.MSSQLTips(Address)
WITH (Online=ON,RESUMABLE = ON,SORT_IN_TEMPDB = ON);

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’.

Next Steps

• Learn more about SQL Server 2019.
• Download and install SQL Server 2019 and start exploring.
• Go through SQL Server 2019 preview release notes.
• Explore more SQL Server 2019 Tips.

About the author

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