Merge replication downloads process quickly but upload has terrible throughput
-
Mittwoch, 13. Februar 2013 21:28
I am running merge replication over a WAN. I am using SQL 2008 R2 SP2. The snapshot applies without issues and small amounts of updates process with no problems. When I try to run larger batches (500 inserts/updates), the latency becomes terrible.
When I run the inserts on the publisher, they sync within 20-30 seconds. When I run them on the subscriber, the sync takes close to 10 minutes.
I have tried the slow link agent profile but don't see any differences in the time it takes. Is there somewhere else I can look to see what is causing this issue?
Any advice is appreciated,
Kim
Alle Antworten
-
Donnerstag, 14. Februar 2013 21:06Moderator Can you copy a file from your publisher to your subscriber and time how long it takes. Then copy the same file the other way. Do you get the same speed?
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Als Antwort vorgeschlagen Allen Li - MSFTModerator Mittwoch, 20. Februar 2013 08:28
-
Montag, 18. Februar 2013 14:53Check if you have blocking on publisher.
Thank you,
Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|
Please click the Mark as answer button and vote as helpful if this reply solves your problem
-
Sonntag, 24. Februar 2013 03:34
The file copy takes the same amount of time both ways.
I tested this using a pull subscription. Downloads were fine but uploads took 10-20 times longer. I switched this to a push subscription. In that test, the uploads were fast but the downloads took the longer time.
If the updates are done on the server where the agent is running, they seem to take 10-20 time longer. I am on a WAN so I expect some latency. I just can't see a reason for it to only be one way.
There is no blocking on either server when this is occurring.
Any other suggestions or better alternatives?
-
Sonntag, 24. Februar 2013 05:45
Connect to subscriber from publisher and run select on some table with large records and check if records are returned immediately,then let me know.The file copy takes the same amount of time both ways.
I tested this using a pull subscription. Downloads were fine but uploads took 10-20 times longer. I switched this to a push subscription. In that test, the uploads were fast but the downloads took the longer time.
If the updates are done on the server where the agent is running, they seem to take 10-20 time longer. I am on a WAN so I expect some latency. I just can't see a reason for it to only be one way.
There is no blocking on either server when this is occurring.
Any other suggestions or better alternatives?
Thank you,
Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|
Please click the Mark as answer button and vote as helpful if this reply solves your problem
-
Sonntag, 24. Februar 2013 18:23Yes, results are immediate.
-
Montag, 25. Februar 2013 15:44Moderator
Kim, it looks like your problem is related to moving data up to your publisher from your subscriber. This is generally a network issue.
However are you using precomputed partitions? You might want to look at these as well.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Montag, 25. Februar 2013 18:45
Thanks Hilary. I have removed all filtering now.
I think I have also isolated this to one table. I ran 10K uploads on various tables in less than 2 minutes.
I ran 10K uploads against one large table (20 million rows) and it's taken almost 2 hours and is still running.
These are all INSERT statements. I have run a trace and see that that particular MSmerge_ins_sp_xxx procedure is running anywhere from 100,000 to 200,000 microseconds (duration in the trace).
-
Montag, 25. Februar 2013 18:48Moderator
Are there any non replication triggers involved?
You might want to look at the execution plan for this proc to see if there are any bad execution plans involved.
Is the batch version of the proc running on the singleton version? The batch with have a _batch extension.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Dienstag, 26. Februar 2013 15:58
There are no non replication triggers.
There is a batch version but it is not being executed. These are single inserts into a log table. The table does have a 'text' column. Since this is still in test, I am going to replace this with a varchar(1000) to see if it makes a difference.
I appreciate all your help with this issue!
Thanks,
Kim
-
Dienstag, 26. Februar 2013 16:04Moderator
Now you mentioned text we know why these are treated as singletons. Varchar(1000) migth not help as well.
can you do this query
select max(datalength(textcolumn)) from problemtablename
if the value is small, perhaps make it this value. Replication will have a problem replicating blobs.
You may find that @stream_blob_columns will help, but generally this only helps for publisher side blob changes.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Als Antwort markiert KimTalley Mittwoch, 27. Februar 2013 13:50
-
Mittwoch, 27. Februar 2013 10:23
Hi
Does indexes on replicated table in publisher and subscriber are the same?
everything is a matter of probability...
-
Mittwoch, 27. Februar 2013 13:49
The actual length used was 1000. I altered this column to be varchar(1000). Previous test for 40K records took 6 1/2 hours. Test after alter took 3 1/2 minutes.
Thanks,
Kim
.png)
.png)
