5

I have read pretty much about index maintenance and all the pros/cons of reorg, rebuild, online, offline, TEMPDB and timing and right now I am kind of more confused than ever before...

The goal is to replace some simple rebuild maintenance plans with a more flexible and fragmentation driven solution to follow best practice and perform required actions instead of not required actions. So following suggestions like this by Kendra Little and a lot of other smart people, I headed for Ola Hallengreen's solution.

So far everything is setup properly, following Brent Ozar's suggestion to create two different jobs (one less-invasive e.g. daily running reorg / online rebuild job and one weekend job that does more and maybe offline processing) and ready to go.

But I am unsure and little concerned about the (side) effects.

Because we have couple of very large tables in place I tried to find out the effects of index rebuilds on transaction logs watching the log file size and number of entris growth based on the following code that I found in this question:

backup log myDB to disk = 'NUL'
-- check log space/usage:
dbcc sqlperf (logspace)
-- query index log records 
SELECT LogRecordsGenerated = COUNT(*)  FROM sys.fn_dblog(NULL, NULL)  WHERE AllocUnitName = 'dbo.TableName.indexName' 
-- COMMINT THE INDEX OPERATION (Online / Offline)
-- Check the above values again...

It is suprinsing that index rebuild OFFLINE tasks require log space and produce transaction log entries while ONLINE does not! I expected it the other way round...?

The questions I can't find answers for are:

  • How will the rebuild online or offline jobs affect Log File Growth? How can I measure this? Is there a better way?
  • How much space do I have to reserve in my data files for the rebuild process? I found hints for around 110%-120% of the indexes size (e.g. in this MS document). In case all my indexes together use about 10 GB, does it mean additional size of 12 GB has to be available in the data file? Or wil lthe index rebuild job that sequentially maintains one index after another re-use the available space and release it after finished an index?
  • In case I use SORT_IN_TEMP, will the transaction log growth happen in the tempdb as well? Also if it is set to SIMPLE recovery mode?
1

Both offline and online reorg/rebuild operations consume log space. This is true for ALL operations that modify a database in any way, even for minimally logged operations such as TRUNCATE TABLE. This is how SQL Server maintains transactional consistency. "Consistency" is impossible unless every action is logged so it can be rolled back or forward if necessary.

Index reorganization uses less log space than an index rebuild. Reorganization defragments the index page-by-page, whereas a rebuild creates an entirely new copy of the index being rebuilt, then drops the old copy of the index. If an individual index is 1GB, rebuilding it will require at least 1GB, plus space for any rollback to take place that may happen as a result of the rebuild either failing or being cancelled. Since any rollback operation will also be logged, log space is reserved prior to the start of all transactions to ensure the transaction can be rolled back successfully. This indicates the maximum potential log space required for an index rebuild would be double the size of the index at the time the rebuild operation begins. So for a 1GB index, you should ensure you can support at least 2GB of log space in use by the rebuild operation for the duration of the operation. This is not a guarantee that much space will be used, it is simply the maximum the operation might need. Any other simultaneous transactions occurring will need additional space.

Any operations that consume tempdb also consume tempdb log space. If you enable SORT_IN_TEMPDB, you need enough room in tempdb log to support sorting the entire index; this amount depends greatly on the structure of the index, including things like how large the key columns are.

If index reorg/rebuild operations are performed serially, that is one-after-another, then you'll need space in the transaction log for the largest index size x 2. i.e. if your largest index is 1GB, to be safe you need to ensure 2GB of transaction log is available for your index operation. If you have simple logging in effect at the time of the reorg/rebuild job, and all operations aren't in a single transaction, transaction log space will be re-used by each individual operation. If you have full transaction logging enabled, and no transaction log backups occur during the reorg/rebuild job, then the log needs to be size at the total size of all indexes to be rebuilt + the size of the largest index to be rebuilt. This can be mitigated somewhat by running log backup jobs during the rebuild process.

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.