SQL Server Non Clustered Index CREATE taking forever to cancel

Nick Ryan 221

I ran a CREATE NON CLUSTERED INDEX command for 1.5 hours. Decided to cancel it because it was stopping other things running. The command has now been running for 22 hours. What is it doing that takes so long to roll back?

The table is 2 billion rows and the clustered index is a columnstore.

The version of SQL Server is 2022.

There are no other jobs running on it (although that is only a recent development).

3 answers

LiHongMSFT-4306 28,041 Reputation points

2024-05-23T06:39:41.9066667+00:00

Hi @Nick Ryan

It will take a while if you have 2 billion rows, the time required to create an index is proportional to the number of rows you have.

What is it doing that takes so long to roll back?

Have you check whether SQL Server has an I/O problem?

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.
Nick Ryan 221 Reputation points

2024-05-23T20:18:47.5833333+00:00

Thanks, Cosmog. I know it will take a long time to create an index on that many rows. What I'm confused about is why it would take more than 20 hours (in fact, ended up being more than 36 hours before it finally finished) to roll back the creation of an index that had only been running for an hour and a half.

There were IO problems as there were lots of other jobs running many of which were blocked by this rollback. We stopped all of them and shut down SQL Server Agent but still the rollback went on and on.

If a non-clustered index is a separate structure, I don't understand why it would take a while to ditch it.

At first we wondered if it had defaulted to doing it as resumable and that it was in some way suspended but I checked the setting on the DB and those options weren't enabled as default. I subsequently found you can't build a resumable index on a table with a clustered columnstore, anyway.

It's done, now, but I'm concerned that if I need to cancel the index creation when I try again, we're going to be stuck for days while it rolls back so I'd love to understand what's happening.

Erland Sommarskog 112.7K Reputation points MVP

2024-05-23T21:24:38.4866667+00:00

It's very difficult to answer why it took so long time in that specific case with no access to the situation. If I would have it in front of me, there may be some good queries to give information of what is going on.

But generally, rolling back can take quite longer time than rolling forward. After SQL Server is optimised for moving forward not backward. I recall a situation many years ago. I was monitoring a transaction that had been running for 30-40 minutes and performed about 40% of the work, and I could determine that it would not fit the transaction log. I killed it, and with help of NOLOCK queries, I can see the progress of the rollback. It took six hours to complete.

Then again, that was a different situation. It was SQL 6.5, and the operation was a loop. I would like to think that rolling back a CREATE INDEX statement is just a matter of deallocating the pages. But may it is not that simple.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Ben Miller-(DBADuck) 190 Reputation points MVP

2024-05-28T23:25:31.34+00:00
It will definitely take a while. The amount of time it runs is not directly related to how long it will take to roll it back if you kill it. But you can use this query during those times to see how it is doing.

If you are on SQL 2017 and above consider using RESUMABLE indexing, if your edition supports it. Then you can just suspend it and resume it later on.

select dt.transaction_id, DB_NAME(dt.database_id) as dbname, dt.database_transaction_log_bytes_used, dt.database_transaction_log_bytes_reserved from sys.dm_tran_database_transactions AS dt INNER JOIN sys.dm_tran_session_transactions st on dt.transaction_id = st.transaction_id WHERE st.session_id = <replace with spid>
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Bruce (SqlWork.com) 66,706 Reputation points

2024-05-29T00:35:29.69+00:00

Rollback is a lot slower than commit. So if you cancel 1 1/2 hours in, it will take several hours to rollback. Don’t restart the sql as this cause the rollback to start over on restart, losing all time you have waited.
Please sign in to rate this answer.
Erland Sommarskog 112.7K Reputation points MVP

2024-05-29T21:20:39.37+00:00

Don’t restart the sql as this cause the rollback to start over on restart, losing all time you have waited.

Nah, it will not start over, because what has been undone, has been undone.

In fact, I have experienced many times that in such situations that restarting SQL Server does speed up things quite a bit, because when the database starts up, the UNDO part is more efficient - since it has the database to itself.

However, things are not always that simple, and if the undo part takes a long time, the database is entirely inaccessible, so it's not a gamble I would recommend on a critical system.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL Server Non Clustered Index CREATE taking forever to cancel

3 answers

Your answer