NEW T-SQL FEATURES IN SQL SERVER 2017- VII | Resumable Online Index Rebuild

Resumable online index rebuild resumes an online index rebuild operation from where it stopped after a failure (such as a failover to a replica or insufficient disk space), or pauses and later resumes an online index rebuild operation

Scenarios where we can use this feature

1. Resume an index rebuild operation after an index rebuild failure, such as after a database failover or after running out of disk space. There is no need to restart the operation from the beginning. This can save a significant amount of time when rebuilding indexes for large tables.

2. Pause an ongoing index rebuild operation and resume it later. For example, you may need to temporarily free up system resources to execute a high priority task or you may have a single maintenance window that is too short to complete the operation for a large index. Instead of aborting the index rebuild process, you can pause the index rebuild operation and resume it later without losing prior progress.

3. Rebuild large indexes without using a lot of log space and have a long-running transaction that blocks other maintenance activities. This helps log truncation and avoid out-of-log errors that are possible for long-running index rebuild operations.

Refer below articles to get more information about this feature:

Syntax

Syntax for SQL Server and Azure SQL Database

--

ALTER INDEX { index_name | ALL } ON <object>  
{  
      REBUILD {  
            [ PARTITION = ALL ] [ WITH ( <rebuild_index_option> [ ,...n ] ) ]   
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]  
      }  
    | DISABLE  
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]  
    | SET ( <set_index_option> [ ,...n ] )   
    | RESUME [WITH (<resumable_index_options>,[…n])]
    | PAUSE
    | ABORT
}  
[ ; ]  

<rebuild_index_option > ::=  
{  
      PAD_INDEX = { ON | OFF }  
    | FILLFACTOR = fillfactor   
    | SORT_IN_TEMPDB = { ON | OFF }  
    | IGNORE_DUP_KEY = { ON | OFF }  
    | STATISTICS_NORECOMPUTE = { ON | OFF }  
    | STATISTICS_INCREMENTAL = { ON | OFF }  
    | ONLINE = {   
          ON [ ( <low_priority_lock_wait> ) ]   
        | OFF } 
    | RESUMABLE = { ON | OFF } 
    | MAX_DURATION = <time> [MINUTES}     
    | ALLOW_ROW_LOCKS = { ON | OFF }  
    | ALLOW_PAGE_LOCKS = { ON | OFF }  
    | MAXDOP = max_degree_of_parallelism  
    | COMPRESSION_DELAY = {0 | delay [Minutes]}  
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }   
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]  
}  

--

Options needed for Resumable Online Index rebuild are-

ONLINE: This is compulsory for Resumable Online Index Rebuild. Hence we need to specify ONLINE = ON.

RESUMABLE:

RESUMABLE=ON means we need the functionality of Resumable Online Index Rebuild.RESUMABLE=OFF means you want to use the normal online index rebuild option.RESUMABLE=OFF is the default value so if you don’t specify this option SQL will use the normal online index rebuild option.

MAX_DURATION : This option specifies the number of minutes that the reusable online index operation will be executed, before being suspended. The time should be > 0 and < 10080 minutes ( Minutes in a Week )

PAUSE: This option pauses the resumable online index rebuild operation. You have execute this command from a separate window.

ABORT: This option aborts a running/paused resumable online index rebuild operation.

Example for Resumable Online Index Rebuild

Data Generation and Table Creation

--

CREATE TABLE testResumableIndex
(
	 ID BIGINT
	,Vals VARCHAR(1000)
)
GO

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) +  (10000*d5.Number)
	+(100000*d6.Number)
	+(1000000*d7.Number)
	 Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4,
	SingleDigits as d5,
	SingleDigits as d6,
	SingleDigits as d7
)
INSERT INTO testResumableIndex
SELECT Number Id , NEWID() Vals FROM Series 
GO

CREATE INDEX Ix_Vals ON dbo.testResumableIndex(Vals)
GO

--

Execute the online index rebuild using the resumable index option-

--

ALTER INDEX Ix_Vals ON dbo.testResumableIndex 
REBUILD WITH (ONLINE = ON, RESUMABLE = ON);
GO

--

Now while the above query is executing open a new query window and execute the below command

--

ALTER INDEX Ix_Vals ON dbo.testResumableIndex PAUSE

--

Now once the above command is complete check the output from the other session

Msg 1219, Level 16, State 1, Line 37

Your session has been disconnected because of a high priority DDL operation.Msg 1219, Level 16, State 1, Line 37 Your session has been disconnected because of a high priority DDL operation.

Msg 596, Level 21, State 1, Line 36 Cannot continue the execution because the session is in the kill state.Msg 0, Level 20, State 0, Line 36A severe error occurred on the current command. The results, if any, should be discarded.Note the below is expected output.

New system view to monitor resumable-online-rebuild-index

MS has included a new system view called sys.index_resumable_operations to check the current status for resumable index rebuild indexes.

Resume – Resumable online index rebuild

You can resume the paused Resumable online index rebuild using below command-

--

ALTER INDEX Ix_Vals ON dbo.testResumableIndex RESUME

--

Now once the above command is executed then you can check the status using the view sys.index_resumable_operations. In this case you will not get any records as we do not have any paused online index rebuild.

Note-
Resumable online index rebuild works only with row store indexes only. They do NOT work with Column Store Indexes.

Refer Microsoft LINK below for more details-

Enjoy !!!

Keep Learning

Pawan Khowal Http://MSBISkills.com