SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Index maintenance generating too much log activity
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rgagne99
Starting Member

2 Posts

Posted - 04/18/2013 :  16:05:59  Show Profile  Reply with Quote
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

USA
15688 Posts

Posted - 04/18/2013 :  16:27:49  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 04/19/2013 :  01:55:42  Show Profile  Visit jackv's Homepage  Reply with Quote
Managing Transaction Logs during Index Maintenace can be a problem. There are various tactics to use - depending on the circumstances. Read this post for various approaches http://www.sqlserver-dba.com/2013/04/manage-transaction-logs-during-index-rebuild.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000