How to troubleshoot merge replication performance issues by using trace flag 101

Introduction

Typically, the Replication Merge Agent takes a long time to replicate changes. To determine which step of the merge replication synchronization process takes the most time, use trace flag 101 together with merge agent logging. To do this, use the following parameters for the merge agent parameters, and then restart the agent:
  • -T 101
  • -output
  • -outputverboselevel


Note If you have to write stats to the <Distribution server>..msmerge_history table, use trace flag -T 102.

More Information

A sample output of the merge agent after merge replication synchronization finishes is as follows:

************************************************************** CONNECTION TIMES --> time took to establish the connection to the servers. Publisher (all connections) 156 msec   Subscriber (all connections) 32 msec Distributor 93 msec ************************************************************** UPLOAD COUNTERS  --> upload phase (changes from the Sub to the Pub) stats MakeGeneration Time = 343 msec. InsertGenHistory Time = 31 msec. UpdateGenHistory Time = 0 msec. ProxiedMetadata Time = 0 msec. ************************************************************** DOWNLOAD COUNTERS  --> download phase (changes from the Pub to the Sub) stats MakeGeneration Time = 219 msec. InsertGenHistory Time = 0 msec. UpdateGenHistory Time = 0 msec. ************************************************************** RETENTION-BASED CLEANUP STATISTICS --> sp_mergemetadataretentioncleanup proc stats Publisher: Cleanup Time 281 msec MSmerge_genhistory rows cleaned up 0 MSmerge_contents rows cleaned up 0 MSmerge_tombstone rows cleaned up 0 Subscriber: Cleanup Time 187 msec MSmerge_genhistory rows cleaned up 0 MSmerge_contents rows cleaned up 0 MSmerge_rowtrack rows cleaned up 0 MSmerge_tombstone rows cleaned up 0 ************************************************************** RETRY STATISTICS Retry Time (Upload) 0 msec. Retry Time (Download) 0 msec. Total changes retried 0 Number of Iterations through rows needing retry 0 Total number of changes that failed despite retry 0 ************************************************************** PROXY METADATA QUEUE COUNTERS Queue Full: Number of Waits: 0, Total Wait Time: 0 msec ************************************************************** Distributor-side History Logging Time = 219 msec. Number of Distributor-side History Messages Logged = 11 Subscriber-side History Logging Time = 295 msec. Number of Subscriber-side History Messages Logged = 11 ************************************************************** 2013-05-28 17:24:11.820 OLE DB Subscriber '< SQL Server name >\sql2008r2': DBCC SQLPERF (NETSTATS)  2013-05-28 17:24:11.822 OLE DB Publisher '< SQL Server name >\SQL2008R2':  DBCC SQLPERF (NETSTATS)  2013-05-28 17:24:11.824 OLE DB Distributor '< SQL Server name >\SQL2008R2':  DBCC SQLPERF (NETSTATS)  NETWORK STATISTICS Server  Reads  Writes  Bytes Read Bytes Written Publisher 74  74  19112  37526 Subscriber 73  73  19032  36931 Distributor 75  75  19192  38121 ************************************************************** NETWORK STATUS Network Connection: The computer has one or more LAN cards that are active. Network link speed: Destination Incoming  Outgoing Publisher Unreachable  Unreachable Subscriber Unreachable  Unreachable Distributor Unreachable  Unreachable ************************************************************** 

References

312292 How to enable replication agents for logging to output files in SQL Server
Properties

Article ID: 2892633 - Last Review: Oct 14, 2013 - Revision: 1

Feedback