CREATE INDEX statement rollback doesn't behave as expected

Question

CREATE INDEX statement rollback doesn't behave as expected

Clint-525719

SSCrazy

Points: 2924
More actions
September 1, 2016 at 9:02 am

#400663

Hello everyone.
Recently, I needed to apply a clustered index to a table. I first performed the operation in a Staging environment. The operation completed in about 20 minutes, so we planned for Prod to take approximately the same time, but we didn't take down any user apps. After I executed the statement, we let it run for about 4 minutes before we noticed some unanticipated blocking that was causing application queries to time out. Due to the timeouts, we decided to stop the execution, so I cancelled it.
Now, I assumed that either one of two things would happen when I pressed the cancel button:
1) SQL Server would stop organizing the data on disk and leave anything it had already moved where it was, thereby causing the rollback to be almost instant.
OR
2) SQL Server would take the data it had already organized on disk and move it back to where it was before, thereby causing the rollback to take approximately as long as the statement had been executing. In this case, the statement had been running for 4 minutes, so the rollback should make the total time 8 minutes.
Well, neither one of those things happened. The rollback actually took 20 minutes by itself, which was the amount of total time the index took to apply in Staging. This made the total time 24 minutes (execution for 4, rollback for 20).
Now, I am no internals expert, but what I infer from this is that after I clicked the cancel button, SQL Server had to scan the entire index on disk to find what data it had moved, and then move it back.
Does anyone have any more insight on this? Am I even in the ballpark here?
FYI, the only thing different between the Staging and Prod environments is that Staging has about 1/6 of the RAM. Also, the table was around 28 million rows and was a heap.
Thanks all!

Viewing 8 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I can't tell you what is happening when creating a clustered index on a heap, or what is happening when it rolls back. What I can tell you, and that you have seen, is that roll back time will not always equal the time a process has run up to that time of rollback.

And this is true for more than just index creation.

ScottPletcher SSC Guru Points: 101192 More actions · Answer 2

Rollbacks very often take more time than the initial modification. They are only very quick if all the pages are still in memory.

Are there nonclustered index(es) on the table? Did you drop it(those) first?

Can you do an ONLINE rebuild?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Clint-525719 SSCrazy Points: 2924 More actions · Answer 3

Thanks for the replies!

There are no non-clustered indexes on that table, and I can't do an online rebuild because I am actually creating the index, not rebuilding it.

Thanks,

Clint

ScottPletcher SSC Guru Points: 101192 More actions · Answer 4

You might need to do the "script out existing nonclustered indexes, create a new table with identical structure -- except with the clus index (only) in place, of course -- copy data to the new table, sync data after the initial copy, rename original table to backup name, rename new table to original table name" thing. Then recreate the nonclustered indexes.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

ScottPletcher (9/16/2016)
You might need to do the "script out existing nonclustered indexes, create a new table with identical structure -- except with the clus index (only) in place, of course -- copy data to the new table, sync data after the initial copy, rename original table to backup name, rename new table to original table name" thing. Then recreate the nonclustered indexes.

Scott, did you read what he said:

Clint-525719 (9/16/2016)
Thanks for the replies!
There are no non-clustered indexes on that table, and I can't do an online rebuild because I am actually creating the index, not rebuilding it.
Thanks,
Clint

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 6

ScottPletcher (9/1/2016)
Rollbacks very often take more time than the initial modification. They are only very quick if all the pages are still in memory.

Since when SQL Server keeps transaction log in memory?

Alex Suprun

ScottPletcher SSC Guru Points: 101192 More actions · Answer 7

Alexander Suprun (9/19/2016)
ScottPletcher (9/1/2016)
Rollbacks very often take more time than the initial modification. They are only very quick if all the pages are still in memory.
Since when SQL Server keeps transaction log in memory?

Even a written page will stay in memory until it is forced out because a page(s) are needed for a different I/O. If the box is relatively static at the time, and/or the transaction is short, the affected log page(s) and/or data pages could still be in RAM.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

CREATE INDEX statement rollback doesn't behave as expected

Cookies on SQLServerCentral