What happens when you cancel or kill a resumable index creation?

SQL Server 2019 adds resumable online index creation, and it’s pretty spiffy:

Those parameters mean:

  • ONLINE = ON means you’ve got the money for Enterprise Edition
  • RESUMABLE = ON means you can pause the index creation and start it again later
  • MAX_DURATION = 1 means work for 1 minute, and then gracefully pause yourself to pick up again later

If you decide you want to pause the index, run this command in another session:

When you do that, the index creation’s session fails with a bit of an ugly error:

Aborted index creation

Okay, well, that’s not exactly what I’d expect from “pausing” something, but that’s okay. There’s a new DMV, sys.index_resumable_operations, that shows the paused index creation:

sys.index_resumable_operations

It shows the syntax used to create the index, the percentage complete, when it was last paused, and more. If you want to give it a happy ending or abort it, it’s your choice:

So far, so good.

The first catch: a paused index is stealthy.

It doesn’t show up in sys.indexes, nor does it show up in the Object Explorer list of indexes on a table. That makes it sound like it’s harmless, but it’s not. I can’t drop the table:

Resumable index operation error

Nor can I create any other index – same error:

Index creation error

Tricky. New stuff for developers to watch out for in their deployment scripts.

The next catch: killing or canceling an index creation, doesn’t.

In SSMS, you’re used to being able to click the “Cancel” button on your query, and having your work rolled back.

You’re also used to being able to kill a query, and have it automatically roll back.

Neither of those are true with resumable index creations. In both cases, whether you kill the index creation statement or just hit the Cancel button in SSMS to abort your request, your index creation statement is simply paused until you’re ready to come back to it. (Or, it’s ready to come back to haunt you, as we saw above.)

So you’ve learned your lesson, right? Don’t use the resumable parameter if you don’t specifically need it. As long as you don’t ask for it, you should be fine…right? Wrong.

The final gotcha: you might get resumable even when you don’t ask for it.

SQL Server 2019 adds these database-level configuration options:

This means whenever you create an index, it’ll default to online creation and resumable even when you don’t specify it as part of your index creation statement! Translation: whenever you go to create any index, if you decide it’s a bad idea and click Cancel, your index creation is still left online partway done, lying in wait to disrupt other operations.

That’s why I’ve added an sp_Blitz check for resumable index operations that have been left behind. It’s in the dev branch now, and it’ll be in the April release. Enjoy!

Previous Post
Updated First Responder Kit and Consultant Toolkit for March 2019
Next Post
What Are You Going to Do About SQL Server 2008?

13 Comments. Leave new

  • Wow. That is somewhat ugly. Like a landmine.
    Great that you are adding it to your tools for visibility.

    Reply
  • Can you add data to table with an index build waiting to resume? It would seem that if you were allowed to a resume would have to basically start over from the beginning.

    This could also be one of those features that started out like columnstore indexes in 2012 where they had way less features then they needed to be truly useful but they got added over time

    Reply
  • Ross Presser
    March 25, 2019 2:08 pm

    Can you drop a paused resumable index? Or are your only choices to either resume it or leave it there?

    Reply
  • Nice post Brent. And being somewhat anal, have to say I don’t like the syntax. ALTER INDEX Blah PAUSE sounds like it should pause the index, which it doesn’t. Why oh why do we keep having new syntax added that doesn’t do what it says? Why not PAUSE_REBUILD or something? I must be getting old.

    Reply
  • Alex Friedman
    March 26, 2019 2:17 am

    Ooooh I love ELEVATE_ONLINE. Very handy for those oops-forgot-to-add-ONLINE-ON-and-blocked-production-forever cases.

    Reply
    • Brian Boodman
      March 26, 2019 6:39 am

      All ELEVATE_ONLINE does is default all index builds to online. The problem is that ONLINE indexes take way, way longer to build, with a nasty performance hit while it is running.

      Reply
  • Robert Barnard
    March 26, 2019 7:07 am

    Great information here. Thank you all.

    Reply
  • I just checked and both of the ELEVATE configuration options default to OFF. That’s a good thing. My foot feels less worried about me accidentally shooting it now.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}