Resumable Index Operations in SQL Server

Leave a reply

SQL Server has long supported the creation, rebuilding, and management of indexes to optimize query performance. However, large databases often face challenges with downtime during these operations. With SQL Server 2017, Microsoft introduced Resumable Online Index Rebuilds, and in SQL Server 2022, the feature was extended to Resumable Create Index Operations.

This functionality allows you to pause and resume index creation or rebuilding operations, giving database administrators (DBAs) more flexibility in managing resources during heavy workloads.

Why Use Resumable Index Operations?

Resumable index operations are particularly useful when working with:

  • Large databases: These operations prevent long-running processes from monopolizing resources.
  • Maintenance windows: Pausing operations allows for better control over system downtime.
  • Intermittent workloads: When you need to stop resource-intensive processes during peak times and resume later.

Key benefits include:

  1. Flexibility: You can pause and resume index operations anytime, mitigating downtime.
  2. Reduced resource impact: Especially useful for systems where resources are limited during specific hours.
  3. Error Recovery: In the case of failure or a system crash, resumable operations can pick up from where they left off.

How to Create a Resumable Index

When creating a new index, you can add the RESUMABLE = ON option to the CREATE INDEX command. Here’s the syntax for creating a resumable index:

CREATE INDEX index_name
ON table_name (column_name)
WITH (ONLINE = ON, RESUMABLE = ON, MAXDOP = 4);

Example:

CREATE INDEX IX_Employee_LastName
ON Employee (LastName)
WITH (ONLINE = ON, RESUMABLE = ON);

In this example:

  • The ONLINE = ON option ensures that the table remains available for queries during the index creation.
  • The RESUMABLE = ON option makes the index creation resumable, allowing it to be paused and resumed.

How to Pause a Resumable Index Operation

Once the index creation is in progress, you can pause it using the ALTER INDEX command. This is particularly useful if you need to free up system resources during a heavy workload.

ALTER INDEX IX_Employee_LastName
ON Employee PAUSE;

How to Resume a Resumable Index Operation

To resume a paused index creation, use the same ALTER INDEX command with the RESUME option:

ALTER INDEX IX_Employee_LastName
ON Employee RESUME;

Monitoring Resumable Index Operations

To keep track of the state of resumable index operations, you can query the sys.index_resumable_operations system view. This view provides information about any index operation that is resumable, paused, or in progress.

SELECT * 
FROM sys.index_resumable_operations
WHERE state = 'PAUSED';

How to Cancel a Resumable Index Operation

If you decide to cancel the index creation entirely, use the DROP INDEX command:

DROP INDEX IX_Employee_LastName
ON Employee;

Example:

Step 1: Start the Index Creation

CREATE INDEX IX_Orders_CreationDate
ON Orders (CreationDate)
WITH (ONLINE = ON, RESUMABLE = ON);

Step 2: Pause the Operation

ALTER INDEX IX_Orders_CreationDate
ON Orders PAUSE;

Step 3: Resume the Operation

ALTER INDEX IX_Orders_CreationDate
ON Orders RESUME;

Step 4: Check the Status

SELECT * 
FROM sys.index_resumable_operations
WHERE object_id = OBJECT_ID('Orders');

For more advanced configurations and further reading on SQL Server 2022’s features, consult the official documentation and SQL Server Tips for regular updates.

Leave a Reply