patternsqlMinor
Reducing Log Impact During Re-Indexing
Viewed 0 times
impactlogduringindexingreducing
Problem
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?
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?
Solution
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:
As you write
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.
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.
Context
StackExchange Database Administrators Q#40376, answer score: 4
Revisions (0)
No revisions yet.