I have a very fragmented database that uses merge replication with remote sites. Do I need to schedule index maintenance (reorganize / rebuild) for all sites? Or just on publisher copy?
1
-
It appears that both answers implicitly suggest that IF I need index maintenance, it is indeed a 'per site' issue to handle. That makes sense as I believe stats are per instance and not replicated.– Dan SorensenCommented Oct 17, 2013 at 22:31
Add a comment
|
2 Answers
The somewhat sledgehammer approach and "best practice" answer would be yes run index maintenance on all sites.
With that said, Index Fragmentation is only truly an issue if it's causing you a noticeable performance penalty. You don't "have to" perform maintenance if there's no real value in doing so.
The performance of each database/instance in the replication topology could be evaluated independently to determine if index maintenance would prove to be beneficial. High fragmentation as a metric in isolation does not necessarily mean that index maintenance would result in a noticeable performance improvement.
By taking into consideration the size of the Index, query data access patterns, storage configurations and query response times, you can establish if index maintenance would prove beneficial and deliver a more refined index maintenance strategy. Assuming it's worth the effort......
It is best practice to maintain indexes on all your instances.
-
It is best practice, here, to provide more detail in your answers. For instance, why is it good to perform index maintenance on ALL instances?– Hannah Vernon ♦Commented Oct 18, 2013 at 14:07