SQL Server – Cancelling Partition Rebuild with Compression

compressionindexrollbacksql serversql-server-2017

Trying to enable compression on a large table in production but we are now out of time (been running for 6.5 hours already).

What is the impact of cancelling the operation and letting it roll back?

Trying to determine if we should power through and let it finish or kill it and let it roll back. However if rolling back a 6.5 hr operation will take just as long, then we may want to just let it finish.

We tested it in QA, which has slower disks, it completed in 4 hours, so we are not entirely sure why this is taking so long.

Things to know:

  • Table size is 271 GB with approx. 30 million rows
  • At the 3 hr mark, the drive got full, we expanded it about 40 min later
  • Prevailing wait type is IO_Completion
  • sp_whoisactive shows CPU and physical_reads increasing, while reads/writes have not moved for quite some time
  • This is on SQL Server 2017 Standard Edition, Windows Server 2016

Here is our statement:

ALTER INDEX [PK_t1] ON [dbo].[t1] REBUILD PARTITION = ALL 
WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, SORT_IN_TEMPDB = OFF
    , MAXDOP = 3, DATA_COMPRESSION = PAGE );

Best Answer

Rollback could be 2x, 4x, etc., since rollback is single-threaded even for operations that were parallel.

My advice would be to let it finish. What's your alternative? Not do it ever? If you can't rely on the estimates your QA system gave, I don't know how you could ever have the confidence to start over and expect a different result.

Related Question