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 );