SQL SERVER – How to Abort Index Rebuilding After Specific Time?

My favorite part of the SQL Server Performance Tuning Practical Workshop is the last part where we open up the floor for questions. Here is one question I received from a DBA last week about index rebuilding.

Question: How to Abort Index Rebuilding After Specific Time?

The user asked wanted to abort index rebuilding operation because he had noticed that sometimes the index was taking just few minutes to rebuild the index, but sometimes it takes over an hour of time when the underlying table on which the index is getting rebuilt is heavily used. They wanted to abort the index rebuilding process if it was taking over 10 minutes of time. They wanted to attempt to rebuild the index after a few hours if the table was very much busy.

Answer: If you are using SQL Server 2014, 2016, 2017 or latest version of SQL Server, you can run the following command and it will abort the index rebuilding process after the period of the time. You can use WAIT_AT_LOW_PRIORITY time keyword along with online rebuild process and it will automatically abort the rebuilding process for the index.

Here is the complete syntax:

USE [AdventureWorks2014]
GO
ALTER INDEX [AK_Employee_LoginID] ON [HumanResources].[Employee] REBUILD 
PARTITION = ALL 
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY
 (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

In the above syntax, you can change the max duration to different value as per your requirement. Additionally, you can also change the value of ABORT_AFTER_WAIT to BLOCKERS and it will terminate all the blocking operations.

One clarification is worth mentioning over here is that the command actually do not abort index rebuilding. The matter of fact the index rebuild will wait for the blocking command to complete before it actually starts rebuilding the indexes. In simple word, if Index Rebuilding does not begin it will abort after specified mentioned time.

Let me know if you have ever used this in your production. I have so far only used at 3 different customer place where they had such requirements.

Reference: Pinal Dave (https://blog.sqlauthority.com)