Post reply

What happens when an online index rebuild (not resumable) is cancelled?

  • October 26, 2023 at 12:13 am

    #4314965

    TLDR:  What is the expected behavior for an index rebuild in online mode (not resumable), which is cancelled on SQL Server 2017?  Is it rolled back completely?  left to be resumed? continues to cause overhead on writes?

    Details:

    Hello community!  It has been a while since I posted.  I have been away from SQL Server for a while, but was recently asked to consult on an unusual problem.  An index rebuild (sql 2017 Enterprise) was accidentally started during business hours with online=On, resumable=Off, on a non-clustered index of a large table.  After noticing that performance was being impacted the DBA cancelled the index rebuild.  Afterward, there was no spid left running a reindexing command, nothing in rollback, and no indications of a plan change, but performance continued to suffer.  Even after a SQL Server service restart, performance suffered.  Only after rebuilding the index in a maintenance window did performance return to normal.

    Some experts involved in our discussions suggested that the temporary index created during the online reindex operation was not rolled back or left in a corrupt state and that SQL server continued to perform poorly because it was maintaining both indexes on write operations, as if the index rebuild was still in progress.  Someone even mentioned that this is a "design feature".  Has anyone had a similar experience?

    Thanks!

  • October 27, 2023 at 1:10 am

    #4315672

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • October 27, 2023 at 2:31 am

    #4315703

    The only time that I've used ONLINE Index Rebuild was during testing on some test tables.  I've never tested for a "cancel" of an ONLINE Rebuild like you folks accidentally had to do.  In other words, I'm probably not the right person to answer questions on this.  I did want to say "Thank you for posting this problem" because I've never heard of it before.

    I'll also say that if folks make such a mistake in the future, remember that Rollbacks are single threaded an can take substantially longer to complete that the ONLINE rebuild would if it had been allowed to complete.

    Anyway, my post will also "bump" your post to the top and, hopefully, someone who hadn't seen it but has an answer will see it and reply.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 3, 2023 at 5:28 pm

    #4320570

    small question I don't understand why you disabled the resumable option

    but this allows you to pause rebuilding

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply