Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rgagne99
Starting Member
2 Posts |
 Posted - 2013-04-18 : 16:05:59
|
| We also 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. Should we go through all tables one by one and try to rationalize specific criteria for Index maintenance in order to dramatically lessen the re-indexing frequency for a large part of the tables? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-04-18 : 16:27:49
|
| We had a similar problem and went with an hourly job. The only solution is to run the script more frequently and have it work on smaller bites, i.e. 1 or 2 tables at a time. For the largest tables you should look at partitioning them and reindexing only specific partitions. We configured our job to reorg for 30 minutes, via Ola's parameters. Larger tables took more than an hour but would typically only reorg once every week or 10 days. The more frequently you do it, the less fragmentation builds up and subsequent maintenance goes faster.Mirroring and AG are a pain with reindexing, there's no way around it. Best advice is to have them running in high-perf/async mode while you do index maintenance. Either that or go with replication or log shipping. Keep in mind that mirroring and AG will synchronize all the reindexing activity, while replication would not. This could vastly reduce the amount of network traffic needed for synchronization, at the cost of increased latency. |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
|
|
|
Index maintenance generating too much log activity