Online rebuild consuming lot of log space

Question

why the online rebuild on a primary key clustered index in taking almost equal to the space of non clustered index size which is untouched.

Details:

• We have a table with primary key clustered index (bigint type) of index size 7GB.
• We have another non-clustered filtered index on same table (varchar(36) type) of index size nearly 1.5TB.
• Online rebuild on primary key clustered index is consuming almost 1.6TB of transaction log size. Options used for rebuild - (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON)
• Another observation, the previous index was created without compression, not sure if that is what causing such a huge growth.
• Could someone please shed some internals about it?

Answer 1

The clustered index is the table. It includes all columns. It is (basically) impossible for the clustered index to be smaller than any one non-clustered index. You probably looked at only the clustered key column size or only the non-leaf level when you read that 7GB size.

So if you rebuild the clustered index, then you rebuild all columns - including the compression setting. Rebuild of the clustered index in the end copies the data to a new location and after its done, it removes the data from the old location.

If you were on 2017, you could do resumable index rebuild, which allow you to pause the rebuild, empty the log and then resume the rebuild again.

Answer 2

REBUILD is an atomic transactional operation so this is expected. If you were to cancel it would need the logged information to roll back.

One possible alternative is to set up a copy table with the compression on and then copy across the rows in batches - of course you still take up extra space in the data file. You can use sp_rename to change the copy table’s name when you are finished