Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Resumable Online Index Create in SQL Server 2019


By:   |   Last Updated: 2018-12-21   |   Comments   |   Related Tips: More > 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.

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.

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.

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.

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

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

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.

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.

ALTER INDEX [NCI_MSSQLTips_1] ON [dbo].[MSSQLTips] 
RESUME;
GO
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.

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’.
Error if we use SORT_IN_TEMPDB option for resumable index.
Next Steps


Last Updated: 2018-12-21


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools