Inserts in Merge Replication database are insanely slow

Question

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?

Deshabille

1. What are the isolation levels being used by insert and syncs ? Usually insert should not be a problem. 2. How many indexes do you have ? 3. Do you have indexes that are not incremental and will cause records being inserted in middle of tree structure instead of the end ? 4. Do you have clustered index ? – Ansell

1. Not sure on isolation levels.. it's just a standard insert statement - 2. We are inserting into table A which has 1 standard index.. however the merge replication triggers stick it into many other tables - 3. Not on the table we are inserting into - 4. No – Deshabille

1. Do you have queries that are being called during replication it might be that replication process blocks whole table in your case heap ( you do not have clustered index ) 2. Is there unique key on the table ? – Ansell

Yes processes and other replication requests come in all the time.. we have about 800 mobile clients who replicate around 10 times a day each so they often overlap each other and the jobs... .. just rechecked. There is no unique key on the table – Deshabille

I would recommend to have clustered index on the table which is sequential and make sure that replication is selecting based on it, and replication and insert should not be running in SERIALIZABLE transaction isolation level, that way you will not have RangeS-S or RangeI-N locks, that are most probably deadlock reasons, if you have deadlock graph. – Ansell

I started tracking indexes and profilers and found something odd that I've included in the question. – Deshabille

Answer 1

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.

Deshabille

Answer 2

we recently experienced the same behavior in our system which is quite simular to yours. The reason was massive amounts of data in msmerge_contents and msmsmerge_current_partition_mappings, and we noticed that it might be a missing index by looking at the number of rows read in SQL Profiler. (49 000 000 reads for a simple insert in one for the tables seemed a little much)

Solved 30 minutes ago by adding two indexes:

CREATE NONCLUSTERED INDEX [IX_MSmerge_current_partition_mappings_PERF1] ON [dbo].[MSmerge_current_partition_mappings] 
(
    [partition_id] ASC
)
INCLUDE ( [rowguid]) 


CREATE NONCLUSTERED INDEX [IX_msmerge_contents_PERF1] ON [dbo].[MSmerge_contents] 
(
    [marker] ASC
)
INCLUDE ( [rowguid])

I hope this can help you, it helped us lower the query time from 5 minutes to 10 seconds.

-- A couple of hours later...

My colleague found another index that further increased the performance by another 75%:

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

Identify missing index You can use the following script to identify missing indexes, sorted with the one expected to increase performance most at the top (There are numerous such scripts circulating, this one was borrowed from http://www.sherbaz.com/category/sqlserver/)

SELECT  sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
,  'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL 
                THEN ''  
    ELSE CASE WHEN mid.equality_columns IS NULL 
                    THEN ''  
        ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL 
                THEN ''  
    ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns 
    FROM sys.dm_db_missing_index_group_stats AS migs 
            INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
            INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID() 
            INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
    WHERE     (migs.group_handle IN 
        ( 
        SELECT     TOP (500) group_handle 
            FROM          sys.dm_db_missing_index_group_stats WITH (nolock) 
            ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
        AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1 
    ORDER BY 2 DESC , 3 DESC

Dive