Resumable Online Index Rebuild

I have been fascinated with another new feature of the upcoming SQL Server release and so I decided to blog about it.

The new version of SQL Server (2017) introduces a very exciting feature for the DBA’s – the Resumable Online Index Rebuild.
This feature is targeting the installations that are doing regular maintenance but which maintenance windows are very limited, and sometimes there is an importance of suspending the indexes rebuild operation. Not canceling it, but suspending or pausing. Sometimes, when the system resources are suddenly needed for a more important business activity.
There have always been a need to have a different option of “everything or nothing” when doing a maintenance for the internal structures of the database, unlike for the data operations where we typically desire for every data manipulation to be successfully executed or to be rolled back entirely.
This new type of the index operation is what Microsoft is bringing in the SQL Server 2017. The Resumable Online Index Rebuild will provide you with an option of executing, suspending, resuming or aborting an online index operation.

In its first iteration the Resumable Online Index Rebuild is supporting Rowstore Indexes only. Boo! – I can hear thousands of voices screaming!
Since SQL Server 2014 there are very few reasons (exceptions) to store biggest tables with any other technologies besides the Columnstore.
The thing is – currently, there is only one online index rebuild operation available for the Columnstore Indexes, and I totally understand why Microsoft decided to focus on the Rowstore Indexes in this iteration.

The syntax for executing the Resumable Online Index Rebuild is described below:

ALTER INDEX { index_name | ALL } ON 
      REBUILD [ WITH (  [ ,...n ] ) ] 
[ ; ]

rebuild_index_option > ::= 
  | ONLINE = { ON [ (  )] | OFF }   
  | RESUMABLE = { ON | OFF }