Search code examples
sqlsql-server-2008merge-replication

Inserts in Merge Replication database are insanely slow


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?


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.