0

The documentation makes resumable index rebuilds sound like magic. For example, it claims

Generally, there's no performance difference between resumable and nonresumable online index rebuild.

and

Resumable index create or rebuild doesn't require you to keep open a long running transaction, allowing log truncation during this operation and a better log space management.

Personally, I've had them save the day once before.

So, assuming:

  • SQL Server 2022
  • Enterprise Edition
  • I have already decided to do my index maintenance online

Why would I ever decide to rebuild an index without making said rebuild resumable?

J. Mini
  • 1,161
  • 8
  • 32

1 Answers1

1

One reason is listed on the documentation:

While an online index operation is paused, any operation that requires a table-level exclusive lock on the table that contains the paused index will fail. This is most often encountered with INSERT ... WITH (TABLOCK) operations. You might see the following error:

Msg 10637, Level 16, State 1, Line 32: Cannot perform this operation on 'object' with ID (objectid) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

To resolve error 10637, remove the TABLOCK hint from your transaction, or unpause the index operation and wait for it to complete before attempting your transaction again.

Ronaldo
  • 6,017
  • 2
  • 13
  • 43