Resumable Online Index Rebuild

Microsoft in the News

With Black Friday, Cyber Monday, and Christmas all behind us I regret I did not mention this sooner. Microsoft has applied some machine intelligence to the process of saving money. The idea here is that if you are going to spend it anyway, Microsoft will help ensure you spend as little of it as possible, and that you are making an informed decision about what it is you are buying.

Bing has a “Shopping Tab” that will search store flyers and keep you informed as to the best deals for items you have on your list. The shopping tab will update in real-time with coupons, product comparisons, reviews and highlights.

Resumable Online Index Rebuild

Resumable online indexes are designed to give you the ability to pause and resume index rebuilds.  This is important when you have a short window of opportunity to get an index rebuilt and it can not be rebuilt in its entirety during that time.  It is also helpful if you suddenly find you need additional resources to complete an index rebuild.

When the rebuild is one that takes a considerable amount of time and resources, this can be a very frustrating event since, typically, you must start the process from the beginning each time. If the issue is resources, the roll back of the process alone can cause additional issues.

Rebuilding your SQL index should be done each time it reaches between 5% and 10% fragmentation. This means you are typically rebuilding at least once a month and depending on the size of your database, the hardware you are running on, the resources allocated, and so much more, it can take a long time to rebuild the index. Any time the rebuild is interrupted and you have to start the process from scratch can lead to frustration, as well as a missed window of opportunity to run the job.

For all these reasons, Resumable Online Index Rebuild was made available for SQL Server 2017.

With this feature, you have much greater control over the Rebuild operation.  Being able to resume the Rebuild process from whatever point the process was paused eliminates a lot of problems.

Some of the problem this solves include:

  • Resume an index rebuild operation after an index rebuild failure, such as following a database failover or after running out of disk space. There’s no need to restart the operation from the beginning. This can save a significant amount of time when rebuilding indexes for large tables.
  • Pause an ongoing index rebuild operation and resume it later—for example, to temporarily free up system resources or to execute a high priority job or query. Instead of aborting the index rebuild process, you can pause the index rebuild operation, and resume it later without losing prior progress.
  • Pause an ongoing index rebuild to allow you to run it over multiple maintenance windows. 
  • Rebuild large indexes without using a lot of log space and have 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 rebuild operations.

For now, my blog has reached the maximum size that will keep people interested in reading. So, I will pause here and in my next blog I will start the process of detailing how to make use of the Resumable Online Index Rebuild feature.