Resumable Online Index Creation: A Must-Know Feature for SQL Server 2019

 

Creating large indexes on production databases is a common task for database administrators. However, it can be a pain, especially when you need to create the index within a short window of time or manage the log growth. In such cases, you may need to change the recovery model to simple, take a full backup after changing the recovery model to full, and enable log dumps. All of these steps can be time-consuming and complex.

Thankfully, SQL Server 2019 introduced a new feature called Resumable Online Index Creation that makes index creation easier and more manageable. This feature allows you to pause the index-building process, truncate or backup the log, and then resume the process in chunks. With Resumable Online Index Creation, you can create large indexes without using as much log space and without long-running transactions that block other maintenance activities.

Resumable Online Index Creation supports the following scenarios:

Resume an index creation operation after a failure, such as after a database failover or after running out of disk space.

Pause an ongoing index creation operation and resume it later, allowing you to temporarily free up system resources as required.

Create large indexes without using as much log space and without long-running transactions that block other maintenance activities.

To use Resumable Online Index Creation, you can simply pause and restart the build of an index at the point where it was paused. For example, if you create an index only to get complaints from users or to manage your log growth, 

you can pause the process by killing the SPID creating the index or by running the command

ALTER INDEX MyResumableIndex ON MyTable PAUSE. 

To restart the process, you can execute the command 

ALTER INDEX MyResumableIndex on MyTable RESUME or simply re-execute your CREATE INDEX statement.

One thing to keep in mind is that SORT_IN_TEMPDB=ON is not supported when using RESUMABLE=ON. Additionally, SQL Server 2019 comes with a new system view called "sys.index_resumable_operations," which shows the percentage complete, current state, start time, and last pause time of the index-building process.

Resumable Online Index Creation is an essential feature for anyone dealing with large databases, especially in OLTP environments. It makes index creation faster, easier, and more manageable. If you're using SQL Server 2019, it's definitely a feature you should explore and use to its fullest.