Replication: Merge Performance Tip


Publisher: SQL Server 2008

Subscriber: SQL Server Compact 3.5

Merge Replication

Performance issue when using keep_partition_changes or precomputed partitions




There could be many causes to this and analysis would have to be done however sometimes it may be due to missing indexes.




In a test environment, use the following indexes and verify performance:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[MSmerge_current_partition_mappings] ([partition_id])

INCLUDE ([rowguid])




— changed and new indexes on system tables

DROP INDEX nc5MSmerge_contents ON MSmerge_contents

CREATE NONCLUSTERED INDEX [NCL_marker] ON [dbo].[MSmerge_contents] ([marker], rowguid)

CREATE NONCLUSTERED INDEX [NCL_partition_id_rowguid] ON [dbo].MSmerge_current_partition_mappings (partition_id, [rowguid])


— update statistics on system tables

update statistics MSmerge_current_partition_mappings with fullscan

update statistics MSmerge_contents with fullscan

update statistics MSmerge_partition_groups with fullscan


— update statistics on all tables involved in join filters

update statistics <table name> with fullscan



Comments (0)