Updated on February 27, 2019: We are delighted to announce that Resumable Online Index Create (ROIC) is now generally available for Azure SQL DB. The feature allows the ability to pause an index create operation and resume it later from where the index create operation was paused or failed, rather than having to restart the operation from the beginning. Additionally, this feature creates indexes using only a small amount of log space. You can use the new feature in the following scenarios:
- Resume an index create operation after an index create failure, such as after a database failover or after running out of disk space. There is no need to restart the operation from the beginning. This can save a significant amount of time when creating indexes for large tables.
- Pause an ongoing index create operation and resume it later. For example, you may need to temporarily free up system resources in order to execute a high priority task or you may have a single maintenance window that is too short to complete the operation for a large index. Instead of aborting the index create process, you can pause the index create operation and resume it later without losing prior progress.
- Create large indexes without using a lot of log space and a long-running transaction that blocks other maintenance activities. This helps log truncation and avoids out of log errors that are possible for long running index create operations.
With this release, we extend the resumable functionality adding this feature to available resumable online index rebuild.
Examples (T-SQL commands):
Create resumable index with MAXDOP=2
CREATE INDEX test_idx on test_table (col1) WITH (ONLINE=ON, MAXDOP=2, RESUMABLE=ON )
Pause a running resumable online index creation
ALTER INDEX test_idx on test_table PAUSE
Resume a paused online index creation
ALTER INDEX test_idx on test_table RESUME
Abort a running or paused resumable online index creation
ALTER INDEX test_idx on test_table ABORT
For more information about ROIC please review the following documents:
- Guidelines for Online Index Operations
- CREATE INDEX (Transact-SQL)
- sys.index_resumable_operations
- Resumable Online Index Operations in SQL video
For further communication on this topic please contact the ResumableIDXPreview@microsoft.com alias.