SQL Server 2019 adds resumable online index creation, and it’s pretty spiffy:
|
1 2 |
CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1); |
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:
|
1 |
ALTER INDEX IX_DisplayName ON dbo.Users PAUSE; |
When you do that, the index creation’s session fails with a bit of an ugly error:
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:
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:
|
1 2 |
ALTER INDEX IX_DisplayName ON dbo.Users RESUME; ALTER INDEX IX_DisplayName ON dbo.Users ABORT; |
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:
Nor can I create any other index – same 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:
|
1 2 3 4 |
ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = WHEN_SUPPORTED; ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED; |
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!
22 Comments. Leave new
Wow. That is somewhat ugly. Like a landmine.
Great that you are adding it to your tools for visibility.
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
Tim – yeah, it’s like an online index rebuild.
Can you drop a paused resumable index? Or are your only choices to either resume it or leave it there?
You can’t drop it because it’s not an index yet, but you can abort it.
So I assume, the index will / can’t be used until done completely (even with partitioning, when it is done with some (the queried) partitions).
Thomas – correct, like I mention in the post, it doesn’t show up in sys.indexes until it’s done.
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.
Thanks Greg! I agree wholeheartedly.
Ooooh I love ELEVATE_ONLINE. Very handy for those oops-forgot-to-add-ONLINE-ON-and-blocked-production-forever cases.
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.
Great information here. Thank you all.
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.
I just had an interesting issue with a partitioned clustered index that i was rebuilding just a partition of.
Partitioned on DATE (varchar) field in YYYYMMDD format.
we have yearly partitions in place : 17 is for data for 2016, 18 for 2017 etc…
i ran “ALTER INDEX [IDX_FK_MyIndex] ON [dbo].[T_PARTITIONED_TABLE] REBUILD PARTITION = 17 WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = ON, MAXDOP = 2) ”
The VM has 6 CPUs and 320GB RAM, and Nimble SAN storage in the background.
I had the usual workload that can handle 1800 transactions per second drop to 50-100 per second.
(the transactions are all INSERTs into the partitioned table-> into partition 22, for 2021)
I left it running for 50 minutes (it had managed 25% of the clustered index on partition 17 (for 2016)), and the backlog was getting too high so i decided to PAUSE it with “ALTER INDEX [IDX_FK_MyIndex] ON [dbo].[T_PARTITIONED_TABLE] PAUSE”
Even with this paused, i only got up to 150/200 transactions/second.
After more fretting and sweating, i used the ABORT command to see if this or some ‘hidden’ workload was causing the slowness. And bam, after the abort it jumped right back up to 1800 trans/sec.
Not a request for support ;), but was curious – I thought partitioned indexes were independent, but the pause of an online resumable rebuild on an otherwise inactive partition affects the entire index, spanning across all partitions ?
Has anyone seen anything like this, or have an explanation? I couldn’t find anything on MS online docs saying anything about this…
For general questions, head to a Q&A site like https://dba.stackexchange.com.
argh ! i triggered the Q&A answer ! sorry, didnt want to be another “hey can you support me” posters – going there now sir !!
ps : glad the GDPR is starting to clarify itself – can’t wait to get some Ozar Training !!!!
Good lord! As if there aren’t enough real issues…
Thanks for taking the time to flush these faults out and then post them, Brent! You ARE the MAN!
Aww, thanks Jeff!
Wow, once again this one of those things you read about in SQL Server, and think AWESOME!!! What we needed! Then the hammer drops. Thanks for the informative post as always Brent.
Hahaha, you’re welcome, sir! Good to have you on the webcast today, and thanks for playing along with the jokes.
LOL NP. You are one of my FAVORITE DBAs because of the jokes, and the ability to explain very complex subjects in layman’s terms.
Awww, thanks!