What happens if you stop the IndexOptimize job, because it is running long and causing blocking?

Question

When the dbo.IndexOptimize job is running for long time and causing blocking, if we stop the job, does it cause Rollback of everything that was reorged or rebuilt? Or it just stop where it is and doesn't proceed further?

We're using SQL Server 2017 Enterprise Edition with an Always On Availability Group.

Answer 1

As stated by others in comments, if an index has been optimized by the IndexOptimize job, then the locks occurring on that index will also come to an end. (Except of course new transactions holding locks on the index)

Answering your questions

...if we stop the job, does it cause Rollback of everything that was reorged or rebuilt? Or it just stop where it is and doesn't proceed further?

Stopping the IndexOptimize job will result in an error message being displayed:

But if you kill the process that is currently performing index maintenance, then that will result in the current index that is being optimized to be rolled back.

Previously optimized indexes are not affected. Only the current index being optimized.

Because you wrote that this is a big table/index, the rollback process can take the same amount of time as has already been used to optimize/rebuild the index.

Enterprise Edition

Seeing as you are using the Enterprise Edition of Microsoft SQL Server 2017 you might want to apply the INDEX_REBUILD_ONLINE parameter in your IndexOptimize job (...from Ola). Using this option will tell Ola's job to trigger the WITH (ONLINE=ON) option.

It is explained in the Microsoft article Perform Index Operations Online as:

The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations. For example, while a clustered index is being rebuilt by one user, that user and others can continue to update and query the underlying data. When you perform data definition language (DDL) operations offline, such as building or rebuilding a clustered index; these operations hold exclusive locks on the underlying data and associated indexes. This prevents modifications and queries to the underlying data until the index operation is complete.

The parameter that is triggered is displayed in the ALTER INDEX documentation on the Microsoft Learn website.

Your job step for the OLA IndexOptimize Job could then possibly be similar to the following:

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = 'INDEX_REORGANIZE',
@FragmentationLevel1=50,
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE',
@FragmentationLevel2=80,
@FragmentationHigh = 'INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE',
@LogToTable = 'Y'

This is essentially telling the job to execute the following:

1. If fragmentation below 50%, then just perform an ALTER INDEX ... REORGANIZE ...
2. If fragmentation is above 50% but below 80%, then perform an ALTER INDEX ... REBUILD ... WITH (ONLINE = ON).... If the index can not be rebuilt online, then just reorganize the index.
3. If fragmentation is above 80%, then perform an ALTER INDEX ... REBUILD ... WITH (ONLINE = ON).... If the index can not be rebuilt online, then perform an ALTER INDEX ... REBUILD ... WITH (ONLINE = OFF) ....

This should enable your Enterprise Edition to gracefully handle large index rebuilds.

Answer 2

A long-running SQL Server maintenance process, like dbo.IndexOptimize, normally tries to roll back the current operations when you stop it. Depending on how the process was going when it was interrupted, your system's response could involve resource contention and blocking. In order to minimize disruptions, maintenance work should typically be scheduled during times of low activity.