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

Article translations Article translations
Article ID: 2892633 - View products that this article applies to.
Expand all | Collapse all

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 msecDistributor 93 msec**************************************************************UPLOAD COUNTERS  --> upload phase (changes from the Sub to the Pub) statsMakeGeneration 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) statsMakeGeneration Time = 219 msec.InsertGenHistory Time = 0 msec.UpdateGenHistory Time = 0 msec.**************************************************************RETENTION-BASED CLEANUP STATISTICS --> sp_mergemetadataretentioncleanup proc statsPublisher:Cleanup Time 281 msecMSmerge_genhistory rows cleaned up 0MSmerge_contents rows cleaned up 0MSmerge_tombstone rows cleaned up 0Subscriber:Cleanup Time 187 msecMSmerge_genhistory rows cleaned up 0MSmerge_contents rows cleaned up 0MSmerge_rowtrack rows cleaned up 0MSmerge_tombstone rows cleaned up 0**************************************************************RETRY STATISTICSRetry Time (Upload) 0 msec.Retry Time (Download) 0 msec.Total changes retried 0Number of Iterations through rows needing retry 0Total number of changes that failed despite retry 0**************************************************************PROXY METADATA QUEUE COUNTERSQueue Full:Number of Waits: 0, Total Wait Time: 0 msec**************************************************************Distributor-side History Logging Time = 219 msec.Number of Distributor-side History Messages Logged = 11Subscriber-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 STATISTICSServer  Reads  Writes  Bytes Read Bytes WrittenPublisher 74  74  19112  37526Subscriber 73  73  19032  36931Distributor 75  75  19192  38121**************************************************************NETWORK STATUSNetwork Connection:The computer has one or more LAN cards that are active.Network link speed:Destination Incoming  OutgoingPublisher Unreachable  UnreachableSubscriber Unreachable  UnreachableDistributor Unreachable  Unreachable**************************************************************

References

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

Properties

Article ID: 2892633 - Last Review: October 14, 2013 - Revision: 1.0
Applies to
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • SQL Server 2012 Enterprise Core
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Evaluation Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbsurveynew kbtshoot kbexpertiseadvanced KB2892633

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com