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. 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.


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!

  • 4
    Rollback is mostly single threaded – Kin Shah Sep 16 '16 at 20:07
  • What version of SQL Server are you using? Are you using Enterprise Edition? What code did you use to convert the heap to a clustered index? – Max Vernon Sep 16 '16 at 20:10
  • We are using SQL Server 2008 R2. Code is: ALTER TABLE dbo.[tableName]ADD CONSTRAINT PK_[name] PRIMARY KEY CLUSTERED ( [columnName] ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] – Clint Sep 23 '16 at 14:23

I believe what you are looking for is in the warning section of this MSDN reference under the Managing Heaps section.

Creating or dropping clustered indexes requires rewriting the entire table. If the table has nonclustered indexes, all the nonclustered indexes must all be recreated whenever the clustered index is changed. Therefore, changing from a heap to a clustered index structure or back can take a lot of time and require disk space for reordering data in tempdb.

Your tempdb is going to play a critical role here and if you already have contention there with normal production requests maintenance tasks like indexing or de-indexing could take significantly more time to complete. And as Kin stated your rollback is probably single threaded vs multi-threaded with the create and alter statements.

  • Well, this certainly sounds logical. Thanks! – Clint Sep 23 '16 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.