Clustered Index Rebuild - Log Usage Estimation

  • Hi

    Running SQL 2017 Enterprise. I am trying to estimate log space requirements for rebuilding clustered indexes ONLINE with SORT_IN_TEMPDB set to ON. To understand what was going on under the hood I emptied the transaction log and issues a REBUILD operation against a partition on a table containing 23,802,158 records. The reserved space used by the index was 1103 MB so row size is ~ 48 Bytes. After rebuilding the index I checked out the contents of the LOG file using the following SQL.

    SELECT allocunitname, operation, context, COUNT_BIG(*) as counts, SUM(CONVERT(BIGINT,[log Record Length])) as [log Record Length]
    FROM sys.fn_dblog(NULL, NULL)
    GROUP BY allocunitname, operation, context
    ORDER BY [log Record Length] DESC

    The results showed that 95% of the log usage (based on aggregated [log Record Length]) was dedicated to the following operation = LOP_INSERT_ROWS, context = LCX_CLUSTERED, details below. One entry for each row in the index, which makes sense given the context.

    operation  context   counts   log Record Length
    LOP_INSERT_ROWS  LCX_CLUSTERED  23802158  3449666256

    The average size of each log entry is ~ 155 bytes, if we subtract 62 bytes (Log Record Fixed Length) and then 48 Bytes (Length of each record in the clustered index in Bytes) we are left with 45 Bytes. Does anyone know what is logged in the remaining 45 Bytes?

    Cheers

    Rob

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply