Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Sorensen Oct 17 '13 at 22:31

2 Answers 2

up vote 1 down vote accepted

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......

share|improve this answer

It is best practice to maintain indexes on all your instances.

share|improve this answer
    
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? –  Max Vernon Oct 18 '13 at 14:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.