Replication: Merge Performance Tip

Problem :

Publisher: SQL Server 2008

Subscriber: SQL Server Compact 3.5

Merge Replication

Performance issue when using keep_partition_changes or precomputed partitions

 

Cause :

 

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

 

Solution/Workaround :

 

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])

GO

 

//--//

-- 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