This is more of a heads up for me / reminder regarding some of the caveats to using ONLINE / RESUMABLE index operations with SQL Server.
Don’t get me wrong, there’s a ton of advantages to using ONLINE and RESUMABLE which I will highlight below but here is the link to the Microsoft Learn page if you want more in depth information
Resumable online index create or rebuild
Pros
- Managing, planning and extending of index maintenance windows. You can pause and restart an index create or rebuild operation multiple times to fit your maintenance windows.
- Recovering from index create or rebuild failures (such as database failovers or running out of disk space).
- Enables truncation of transaction logs during an index create or rebuild oper
- This is fantastic for environments using Always On Availability Groups
Cons
- When an index operation is paused, both the original index and the newly created one require disk space and need to be updated during DML operations.
- SORT_IN_TEMPDB=ON option is not supported
- Disk Space:
- At least the same amount of space again required for Clustered Indexes
- This is on top of temporary mapping index
- Performance:
- Resource usage increases due to both source and target structures being in place – up to 2x
- This can be a huge issue for some very busy environments
One thought on “Resumable Online Index Operations”
1 Pingback