Not sure that I like the message from Online Resumable Index Rebuild in SQL Server 2017

I was messing around with the Online Resumable Index Rebuild, this is new in CTP 2 of SQL Server 2017. I don't know that I like the output from an resumable index rebuild

Let's take a look at what I did
First I created this table

CREATE TABLE dbo.TestIndexRebuild(
 name nvarchar(35) NULL,
 number int NOT NULL,
 type nchar(3) NOT NULL,
 low int NULL,
 high int NULL,
 status int NULL,
 somebigchar char(2000)
) ON [PRIMARY]

CREATE CLUSTERED INDEX CI_TestIndexRebuild ON TestIndexRebuild(name,number)

I made the table wide by adding a 2000 character column, I then added a clustered index to the table

I then pumped in a bunch of data

INSERT INTO TestIndexRebuild
SELECT *,REPLICATE('A',2000)  
FROM master..spt_values
GO 500

I then executed the following

ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild 
REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=1) 

Here is the output I got after 1 minute

Msg 3643, Level 16, State 1, Line 19
The operation elapsed time exceeded the maximum time specified for this operation. The execution has been stopped.
Msg 596, Level 21, State 1, Line 18
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 18
A severe error occurred on the current command.  The results, if any, should be discarded.

I don't really like that, index rebuild has been paused because elapsed time has exceeded the maximum time displayed in black instead of red would have been fine with me. I don't need to see that the session is in a kill state or that a severe error occurred

I then executed the same command again

ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild 
REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=1) 

Here is the output

Warning: An existing resumable operation with the same options was identified for the same index on 'TestIndexRebuild'. The existing operation will be resumed instead.

While that was running in a second window, I executed the following

ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild  ABORT 

All you get as output is

Command(s) completed successfully.

However, in the widow where you executed the resumable index rebuild you get this

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

If you execute the ABORT again while the index is not being rebuilt, you get

Msg 10638, Level 16, State 2, Line 1
ALTER INDEX 'ABORT' failed. There is no pending resumable index operation for the index 'CI_TestIndexRebuild' on 'TestIndexRebuild'.

That makes sense


With a PAUSE Same thing happens as with ABORT, when you pause the index rebuild

ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild  PAUSE

All you get as output is

Command(s) completed successfully

But you get those other messages in the original window


You can also execute a PAUSE followed by an ABORT, you will only get one set of messages, no error is displayed in the window where you executed the code below

ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild  PAUSE 
GO
ALTER INDEX CI_TestIndexRebuild on TestIndexRebuild  ABORT 

I think the kill state and severe error occurred is a little over the top.
What is your opinion?


Now having said all that, I do like the resumable index rebuilds, it pretty much mimics the defragment/reorganize functionality. It continues from where it was when if was running last

There are some more things you can specify, for example, here is a sample command

ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP=2, MAX_DURATION= 240 MINUTES, WAIT_AT_LOW_PRIORITY (MAX_DURATION=10, ABORT_AFTER_WAIT=BLOCKERS)) ;


Read more about this in section J of ALTER INDEX