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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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......