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