Last week Microsoft confirmed that the name of SQL vNext will indeed be SQL Server 2017. I was looking through the documentation for more info on Adaptive Query Processing as I wanted to write a post on adaptive joins but then I saw: –

To see the new Adaptive Join operator in Graphical Showplan, a new version of SQL Server Management Studio is required and will be released shortly.

Source

Ok, I’ll wait! So I went back to the list of new features in SQL 2017 and something else caught my eye. The ability to pause and then resume online index rebuilds.

Sounds pretty cool, let’s see it in action. Here’s the setup: –

(SQL Server 2017 can be downloaded from here btw)

USE [master];
GO
CREATE DATABASE [Test]
GO
ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 140
GO

That’s the database created and put into the correct compatibility level (just to be sure). Now let’s create a table to test with: –

USE [Test];
GO
CREATE TABLE [TestTable]
(PKID INT IDENTITY(1,1) CONSTRAINT PK_Test PRIMARY KEY CLUSTERED,
 ColA VARCHAR(10),
 ColB VARCHAR(10),
 ColC VARCHAR(10),
 ColD DATETIME)
SET NOCOUNT ON;
INSERT INTO [TestTable]
(ColA,ColB,ColC,ColD)
VALUES
(REPLICATE('A',10),REPLICATE('B',10),REPLICATE('C',10),GETUTCDATE())
GO 100000

OK, now let’s rebuild the clustered index that we’ve created on the table: –

ALTER INDEX [PK_Test] ON [TestTable] REBUILD WITH (ONLINE=ON,RESUMABLE=ON);
GO

N.B. – notice the new option RESUMABLE=ON

Whilst that’s running, open a new connection and run: –

USE [Test];
GO
ALTER INDEX [PK_Test] ON [TestTable] PAUSE;
GO

The session that running the rebuild should now have stopped with the rather disconcerting error: –

Looks pretty worrying imho! But not to stress, jump back to your other connection and run: –

ALTER INDEX [PK_Test] ON [TestTable] RESUME;
GO

Hmm, but how do I tell that this has worked? Well, if you have sp_whoisactive on your instance you can verify that the query is re-running: –

Pretty cool, huh? Full information on this can be found here: – https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

I think this is very useful but we do need to be careful. The documentation says that pausing an online index rebuild for a long time may affect query performance and disk utilisation. This is due to the newly rebuild index being created side-by-side to the original one so we’ll need to watch out for that.

Thanks for reading!