Inserts in Merge Replication database are insanely slow
merge-replication, sql, sql-server-2008
Solution
In the end indexes only helped so far, it looks like merge replication isn't setup very well on this system.
However using Bulk Insert without firing triggers and then use sp_addtabletocontents resolved our issue.
As a side note we had to do a basic update
Update Table set Column1 = Column1
after we did the bulk insert so that merge replication notified the other linked tables that it had changed else not all the data propagated correctly.
Problem
I have a SQL server that is setup with merge replication to 800 mobile clients running SQL CE. The server has enough resources and the lines in and out of the company are more than adequate, the replication between clients and the server is generally good but we are getting an intermittent error that I just cannot track down. Yesterday we needed to insert 550 records into one of our main tables, the only triggers that exist are the standard merge replication ones. This insert took 14 hours due to it continually getting deadlocked with mobile devices trying to synch. Does anyone have any advice on how we can avoid locks on inserts and how to speed up the entire process? ------ Update ----- Following on from some comments I've run a profiler over a single insert and I'm seeing a lot of this sort of thing ``` insert into dbo.MSmerge_current_partition_mappings with (rowlock) (publication_number, tablenick, rowguid, partition_id) select distinct 1, mc.tablenick, mc.rowguid, v.partition_id from dbo.MSmerge_contents mc with (rowlock) JOIN dbo.[MSmerge_JEMProjectME_PromotionResource_PARTITION_VIEW] v with (rowlock) ON mc.tablenick = 286358001 and mc.rowguid = v.[rowguid] and mc.marker = @child_marker and v.partition_id in (select partition_id from dbo.MSmerge_current_partition_mappings cpm with (rowlock) JOIN dbo.MSmerge_contents mc2 with (rowlock) ON cpm.rowguid = mc2.rowguid and mc2.marker = @marker) where not exists (select * from MSmerge_current_partition_mappings with (readcommitted, rowlock, readpast) where publication_number = 1 and tablenick = 286358001 and rowguid = v.[rowguid] and partition_id = v.partition_id) ``` For many tables that I'm not meant to be inserting into... could this be a clue?