Replicated Database Index Maintainence

  • Guys,

    I have 200GB database which is replicated, the secondary database is subjected to searches (read only) operations. Each day there are up to 500k (up to 200k of them are deletes) transactions that are replicated real time. How should the index maintainence plan be setup since all the transactions are replicated agent, is index rebuild advisable on each night to maintain the reponse times of the searches consistently.

    Any suggestions and inputs would be help

    Thanks

  • I'd treat it like any other system. Put an index maintenance routine in place that reindexes or reorgs the indexes as needed based on their size and fragmentation levels. Because it's replicated doesn't change the fact that it's receiving inserts, updates & deletes just like any other database. And just like any other database, the indexes are going to fragment over time. I'd also be sure to update the statistics on a regular basis.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply