Sql-server – Index rebuild log growth

azure-sql-databaseindexsql servertransaction-log

How much log index rebuild will generate.I remebered reading rebuild index should generate same amount of log file as table size.But my tests are showing otherwise.We need this estimate since we are trying to build azure database indexes and it has a limitation of max 2 GB.

My database is in full recovery model.

Table Size:

enter image description here

log size:

enter image description here

from the pictures you can see log generation was very less with online index rebuild operation.Can some one correct me if i am missing anything

Best Answer

If you are rebuilding a clustered index, you are essentially rebuilding the table, but a non-clustered index is just a subset of the table (usually). If you use the SORT_IN_TEMPDB option, you will offload some of the logging to the tempdb transaction log. Version will also matter, in 2005 online rebuilding is minimally logged but was changed back to fully logged in 2008. You might also want to take a look at this article which might be useful in deciding whether to rebuild or reorganize with the log size in mind.

----Update----

Using Shanky's test setup I performed the same steps, but I added a few extra size checks. Before the rebuild, I perform a log backup and check the log space and utilization as well as the records in the transaction log for my index. Then I do the rebuild test and recheck the log size and the number of log records:

backup log IndexLogging to disk = 'NUL'
--check log space/usage
dbcc sqlperf (logspace)
--check log records for the index, exclude the where for the total number of logs generated
SELECT LogRecordsGenerated = COUNT(*) 
FROM sys.fn_dblog(NULL, NULL) 
WHERE AllocUnitName = 'dbo.Logging.IX_LOGGING' 
--Perform your rebuild operation and then recheck the same parameters

I tried this about a dozen times and each time I get the same results, the DMV is only telling you for the transaction, but there are several nested transactions that are spawned as a result of the log_test transaction that don't show up including the allocation of extents and pages and the insertion of the data. If you look at the actual contents of the log file, you can see how the offline rebuild is very efficient. It just allocates the pages/extents, formats them and sets them and then deallocates the old ones. An online rebuild is doing a lot more work since it has to keep the index available while it rebuilds. This is probably more evident in the locking: offline it locks the entire object but online it has to go page by page, key by key. You can take a look for yourself to compare:

SELECT
OPERATION,
Context,
[Transaction Name],
[AllocUnitName],
[AllocUnitId],
[Page ID],
Description,
[Lock Information], [Transaction ID]
FROM FN_DBLOG(NULL,NULL)

You can reduce the log generated by temporarily switching to BULK_LOGGED while your index maintenance runs, but you lose the ability to do point in time recovery for that period of time. Apparently it also does not reduce your log backup.

--More info on log usage from DBCC SQLPERF (logspace) and SELECT LogRecordsGenerated = COUNT(*) FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitName = 'dbo.Logging.IX_LOGGING' to show actual log sizes, note the index is only 648KB

This is the log after populating it with data:

Database Name Log Size (MB) Log Space Used (%) Status

IndexLogging 14.99219 70.89956 0

LogRecordsGenerated

10903

Here is the log after a log backup:

Database Name Log Size (MB) Log Space Used (%) Status

IndexLogging 14.99219 5.100313 0

LogRecordsGenerated

0

Log after log backup and then Online rebuild:

Database Name Log Size (MB) Log Space Used (%) Status

IndexLogging 14.99219 22.642 0

LogRecordsGenerated

11160

Log after log backup and then offline rebuild:

Database Name Log Size (MB) Log Space Used (%) Status

IndexLogging 14.99219 10.79338 0

LogRecordsGenerated

140

Related Question