none
Index Maintenance in a Replicated Environment RRS feed

  • Question

  • We have a sizeable database. We replicate about a third of it to another cluster and use that data for reporting. Previously we had a script that ran on a schedule to perform index maintenance. The script looked at percentage fragmentation and if it was > 40% it would do a rebuild, < 40% AND > 10% it would do a reorganize. Reorganize is a logged operation. This is important for later in the thread. 

    At some point before I arrived, the less than greater than logic was commented out, and what was left in was >10% do a reorganize.

    Recently we have been experiencing explosive growth and our data volume has doubled in two years. We have had issues with replicating large updates that we believe cause the log distribution agent to time out on. We are also seeing cases of the log reader needing to skip large chunks of the transaction log typically when we run a long logged operation like reorganize.

    My question is what are folks doing for index maintenance in a high volume OLTP environment where you are using replication?

    Thanks

    Shawn


    Monday, May 22, 2017 9:51 PM

Answers

  • We are very careful about re-indexing in general. Check fragmentation before and after you do re-indexing to see if it makes a difference. Check to see if the indexes are actually in use. Also check to see how the indexes are being used, are they primarily being used for seeks or scans. If they are being used for scans they are more sensitive to fragmentation. if they are being used for seeks, they are less sensitive to fragmentation.

    Index and table scans are symptomatic of a reporting/dw workload. Does this really belong on your publisher - consider offloading it.

    You may also find that it is faster to re-initialize publications or articles if they do generate significant logging activity. If you partition your tables, you can rebuild indexes on individual partitions, this pay dividends when you have partitions which are hot, and partitions which are essentially read only.

    • Marked as answer by swt1974 Tuesday, May 23, 2017 6:53 PM
    Monday, May 22, 2017 10:08 PM
    Moderator

All replies

  • We are very careful about re-indexing in general. Check fragmentation before and after you do re-indexing to see if it makes a difference. Check to see if the indexes are actually in use. Also check to see how the indexes are being used, are they primarily being used for seeks or scans. If they are being used for scans they are more sensitive to fragmentation. if they are being used for seeks, they are less sensitive to fragmentation.

    Index and table scans are symptomatic of a reporting/dw workload. Does this really belong on your publisher - consider offloading it.

    You may also find that it is faster to re-initialize publications or articles if they do generate significant logging activity. If you partition your tables, you can rebuild indexes on individual partitions, this pay dividends when you have partitions which are hot, and partitions which are essentially read only.

    • Marked as answer by swt1974 Tuesday, May 23, 2017 6:53 PM
    Monday, May 22, 2017 10:08 PM
    Moderator
  • In addition what Hilary wrote.

    You may use higher than 10% fragmentation values for reorganize, add delay between index rebuild (it will allow log reader to scan and read transaction log). You can also increase -LogScanThreshold and -REadBastchsize in you logreader agent profile.

    Tuesday, May 23, 2017 12:01 PM