When managing large databases, maintaining indexes in SQL Server is crucial for performance. However, traditional index rebuilds could not be paused and had to be restarted if interrupted. SQL Server introduced a helpful feature that allows online index rebuilds and creations to be paused and resumed. This can save time and resources, especially when maintenance windows are tight or interruptions occur. 

Why Resumable Indexes Are Useful 

This functionality is particularly helpful in scenarios such as database failovers, low disk space, system performance issues, or reducing the size of transaction logs. It provides database administrators with greater control over index operations that take a long time to complete. 

Key Features of Resumable Index Rebuild 

The key actions you can perform include: 

• Start a resumable index rebuild or creation 
• Pause the operation if needed 
• Resume the operation later 
• Abort the process entirely 

Exploring a career path in SQL?
Get started with our Oracle SQL Training

Basic Syntax 

Here’s a simplified example of the syntax used to enable resumable operations: 
 
ALTER INDEX [index_name] ON [table_name] REBUILD WITH ( 
  ONLINE = ON, 
  RESUMABLE = ON, 
  MAX_DURATION = 60 
); 
 
You can also pause or resume using: 
 
ALTER INDEX [index_name] ON [table_name] PAUSE; 
ALTER INDEX [index_name] ON [table_name] RESUME; 

Practical Example 

Assume we are using an Azure SQL Database and want to test how the resumable feature behaves. First, we run a command to rebuild the index using the RESUMABLE = ON option. While it’s in progress, we pause the operation from another session. This simulates a situation where a long-running process needs to be halted temporarily. 

Checking Index Status 

SQL Server offers a system view called sys.index_resumable_operations that provides details on the current state of all active resumable index operations. Using this view, you can track whether an operation is running, paused, or aborted. 

Conclusion 

Resumable index rebuilds and creation offer flexibility and control when managing indexes in SQL Server. By leveraging this feature, you can better handle interruptions and system resource limitations without losing progress. 

Author: Mayank Minal is a Principal Consultant at Fusion Practices