0

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?
Improve this question
3

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.

Improve this answer
3
  • Suppose if my table size is 2TB (1TB data space + 1TB of index space), does it means the transaction log could grow to it's max 2TB of size when online rebuild is performed? Totally makes sense for a data file as it makes a copy during online rebuild, but why the log will grow that huge? – Siva Dasari Apr 15 at 17:58
  • The log will grow because all modifications are... logged. If you set recovery mode to simple or bulk logged, then you will have a minimally logged operation instead. But talk to your DBA first, since it affects your backup routine. – Tibor Karaszi Apr 15 at 18:01
  • got it, thank you. – Siva Dasari Apr 15 at 18:14
0

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

Improve this answer

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.