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?