September 1, 2016 at 9:02 am
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!
September 1, 2016 at 10:31 am
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.
September 1, 2016 at 11:10 am
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".
September 16, 2016 at 1:37 pm
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
September 16, 2016 at 3:12 pm
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".
September 16, 2016 at 3:23 pm
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
September 19, 2016 at 12:22 pm
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?
September 19, 2016 at 12:38 pm
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".
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy