2

I got hired into a new company and they had merge replication already in place. They have had issues with it to the point I have to evaluate whether to keep it or replace it. The main issue is it takes many hours (over 12 hours in some cases) to insert or update a record. Honestly that is just unacceptable.

As an example I am trying to insert one record into a table (ComputerUsers) that links a laptop to a user. You only have two columns (one is call ComputerID and one is called UserID). There is a Computers table that stores the ComputerIDs and the Computername. There also is a Users table that stored the UserID and username.

All I am trying is insert one record into the ComputerUsers table. Last time I ran it it took over 13 hours. This table is published and it is part of the Dynamic Filter.

I have tried everything I have read online. I created two separate indexes for the UserID and ComputerID columns since they are both included in separate joins in the Filter. There is also a clustered primary key on the Userid and ComputerID columns since the combination of the two are unique (A computer can be assigned to two users or vica versa). I have also have tried rebuilding the indexes and even created some suggested indexes on the merge replication tables.

I am at my wit's end. What am I missing? I find it hard to believe this is normal for Replication because I doubt anyone would use it if it was. Thanks.

Improve this question
5
  • What service pack you are running 2008 SQL server ? What edition - standard, enterprise? - for pub, subscriber and distributor.
    – Kin Shah
    Commented Mar 24, 2015 at 20:41
  • Also, try running sp_whoisactive when you are doing an insert - what wait types are associated, how many CPU, memory, etc.
    – Kin Shah
    Commented Mar 24, 2015 at 20:42
  • Have you checked the overall performance of the server? Is there blocking? When was the last time statistics were updated?
    – stacylaray
    Commented Mar 24, 2015 at 20:53
  • I am running SQL Server 2008 R2 Service Pack 1 Standard Edition. Do you applying service pack 3 would help? I also forgot to mention that I only do these inserts/updates on off hours because if I did it when people were in the database it would knock them out.
    – Chris Patrick
    Commented Mar 24, 2015 at 23:06
  • In all my years doing merge replication, it comes down to joins defined, and indexes. That is where you need to start.
    – Greg
    Commented Feb 5, 2017 at 2:31

1 Answer 1

Reset to default
1

I had a very similar sounding problem. We replicated a table with millions of rows. One of the subscribers would take up to 2 minutes to insert 100 new rows.

I solved the issue by adding an index to the rowguid column of the table it was inserting to. I know this sounds like it should degrade performance but it looked like it was doing a full table scan before inserting, probably to make sure a duplicate row was not inserted.

Once I added this index replication was running smoothly again

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.