SQL Server Merge replication makes very heavy use of metadata tables, and these are likely to become fragmented quickly. My own totally unscientific findings are that regular maintenance of these tables can give a 10% reduction in sync times.
If you want to know more about the subject read this article from BOL:
http://msdn.microsoft.com/en-us/library/ms151789(v=sql.105).aspx
You should be aware that most maintenance systems including the excellent and very popular Ola Hallengren solution http://ola.hallengren.com and the built in SQL Server Maintenance Plan system did not include the merge replication metadata tables.
Whilst investigating Ola's solution I noted that it did not include merge metadata tables which are marked as MS_Shipped objects, so I contacted Ola and he listened carefully, asked some very good questions and decided to add as a feature. The feature is now available in his build and you need to look at the parameter MSShipped objects.
It is commonplace to say the SQL Server built in Maintenance Plan is poor, as it rebuilds all indexes without regard to levels of fragmentation or size of tables etc. This is true but if you are not running 24 x 7 and the extra log generation is not going to create many problems (remember you will be creating/backing up a very large log file with the the built in maintenance plan in full recovery) and you have some disk IO to burn it is a good start and certainly much better than nothing. Take the option for compression if you have a supported version (SQL Server 2008R2 Standard and higher).
Whatever you are using check fragmentation of merge metadata tables.
You can read more here:
http://www.brentozar.com/blitz/merge-replication-with-infinite-history/
No comments:
Post a Comment