Reducing Log Impact During Re-Indexing

Question

We use Ola's maintenance solution and its great.

Regardless of method for re-indexing a major friction point with IT is the amount of log generated during the weekly re-indexing process. For a 1TB DB upwards of 300 GB of log can be generated. This causes mirroring backlogs/delays and also cause Data Protection Manager to take a long time to sync up with its off-site DPM partner server (sometimes several Days!). As we approach a time where we will have a Second site on warm standby we know that this delay in having off-site backups available during the vulnerable period after Index maintenance could be the Achilles heal. We are considering a larger pipe between the sites for Avail Group but to generate less burst Log activity would be great.

To mitigate this we have done 2 things with only minimal impact. First we spread out the weekly re-indexing by introducing Delays, purposely slowing a 3 hour process to about 8 hours or so. Secondly "some" key tables are maintained by a process that runs hourly resulting in Just in Time re-indexing.

In a large and active OLTP DB with some LOB what are the rule of thumbs for re-indexing frequency, % of database affected, # indexes that should be rebuilt less frequently? Is weekly rebuild overkill?

Answer 1

For a 1TB DB ... Is weekly rebuild overkill?

I found it hard to believe that your OLTP system goes over the course of a single week and reshuffles 1TB of data so bad as to require a rebuild. Very few use cases would ever call for something so dramatic. Unless you have an explanation why do you need such high frequency I will say no, one week is way way too often.

are the rule of thumbs for re-indexing frequency, % of database affected, # indexes that should be rebuilt

Yes, the same rule of thumb that applies to almost every performance related action: you measure impact. You establish a performance baseline, you measure the deviation from it over time, and you measure the impact of reindex actions. W/o basic measurements is always going to be a shot in the dark.

As for the original question: how do you reduce the amount of log generated during reindex? There are only two viable solutions:

• use minimally logged operations
• rebuild less, which require partitioning

As you write causes mirroring backlogs/delays it means option one is off the table as minimally logging and mirroring don't mix. That leaves only the option to rebuild less. Partition rebuild can come in handy, but only offline partition rebuild is supported. You can minimize the offline time by using fast partition switch (ie. you rebuild a copy of the data and then you switch the optimized rebuilt data).

Ultimately, for 1TB, you should never rebuild it. Your old data, which never changes, should lay at rest, compacted and archived. Current data is subject to churn and changes and you should only have to rebuild what has changed.

Answer 2

As a general rule of thumb, you should reorganise your indexes when they reach between 5 and 10 percent fragmentation, and you should rebuild them once that number reaches around 25 to 30 percent.

You should also ensure that your indexes have the appropriate fill factor set for the amount of inserts / updates they receive. Good rule of thumb for that is:

• Very few updates - 100%
• Very frequent updates - 50% to 70%
• ... and somewhere in-between for the rest...

Getting the fill-factor right, will minimise fragmentation due to page splits.

The above will help you deal with your problem more efficiently, but it might not solve the root cause. For e.g. if you experience high log file growth during re-indexing, it might be because your database is in the FULL recovery model. It is good practise to put the database in BULK_LOGGED recover model before the re-index, and switch back to FULL afterwards.

SQLFool has created a handy script that automates the re-organise / re-index for you based on the current fragmentation that you might be able to adapt to your needs. It can be found HERE.