Merge Replication with Infinite History
sp_Blitz Result: Merge Replication with Infinite History
When merge replication is in use it creates a number of metadata/tracking tables that store the changes to your replicated tables. These change tracking tables can get very large so SQL Server helpfully trims them on your behalf after a time that you can set – this time is called the retention period. By default the retention period is set to 14 days so you have to sync a subscriber at least every 14 days. The tables always trimmed are:
- MSmerge_contents
- MSmerge_tombstone
- MSmerge_genhistory
If you are using filtering the following additional tracking tables are created and trimmed:
- MSmerge_partition_groups
- MSmerge_current_partition_mappings
- MSmerge_past_partition_mappings
When you are starting out it can seem a good idea to always retain the data ‘just in case you need it’ and forget to change the setting back. This is always usually a bad idea. If these tables are never trimmed they can cause significant performance problems.
To Fix the Problem
If you do have an infinite retention period and huge metadata/tracking tables you will need to trim it back to an appropriate level suitable for your application. Don’t just set it back to your new value in one step as you could end up with a major problem on your next sync as SQL Server tries to delete millions and millions of rows in one go. Take the retention period down slowly until you reach your chosen value.
Retention periods are discussed in BOL: http://msdn.microsoft.com/en-us/library/ms151778(v=sql.105).aspx
If you can safely keep less than the 14 days default metadata then do so and reap the performance benefits.
While you’re at it, make sure that your maintenance solution is maintaining these tables as they are likely to be some of the hottest tables in your system – potentially logging every change to many tables.
If you use the built in SQL Server Maintenance Plan (or any solution that excludes tables where the is_ms_shipped field = 1) for example these tables are NOT included for maintenance and you will have to maintain them yourself. Rebuilding these tables can cause application performance issues so think carefully when/how to maintain them. Keeping your merge tracking tables in top shape will benefit your application performance and reduce sync times.
If you’re using the very popular Ola Hallengren maintenance scripts, check out the option to include MSShipped objects. When enabled, this will reindex the tracking tables for you. (This was added in his May 2013 version.)
Contributed By Nigel Maneffa
Big thanks to Nigel Maneffa (Blog) for contributing the explanation and the check!