“The Only Thing That Is Constant Is Change”

Approach for Transactional Replication High Latency.

Posted by database-wiki on July 15, 2012

One could experience High Latency moving data to a subscriber using Transactional Replication

On a Transactional or Peer-2-Peer Replication Scenario you may observe a delay in the time it takes for a transaction to move from the Publisher to a Subscriber. The problem could be a spike or a sustained latency.


Analyzing SQL Transactional Latency by first determining if the problem exists with the LogReader or the Distributor. Then further analyze the workload to determine if the problem exists in the Agent’s Reader or Writer thread. Work includes collecting the following Replication configuration data, enabling Agent logging, collecting Windows Perfmon, SQL Profiler data and SQL DMVs.

Required Data:

. Provide description of various Transactional Replication Agent parameters which can be used to Tune Agent performance

. Collecting Replication Publication Scripts

. Collecting Replication Publisher Metadata

. Collecting Replication Distributor Metadata

. Collecting Replication Subscriber Metadata

. Restarting Agent to collect LogReader and Distributor verbose log

. Restarting Agents with HistoryVerboseLevel 2 to provide additional Agent performance details in the Distribution database Agent History Tables

. Collecting PSSDIAG/Perfmon data during observed problemto track workload load Replication Stored Procedures.

. Collecting DMV execution stats on Replication stored procedures to determine IO and Duration.

. Generation of TracerTokens to focus and divide the investigation into LogReader vs. Distributor

. Analysis of the LogReader Reader/Writer threads:

. Review LogReader/Distributor History to understand workload and delivery rates

. Review PSSDIAG Reads/Write/CPU/Duration on RPC:Completed for Key Replication stored procedures

. Checking for high IO queries and overall data volumes.

Self approach:

. Tweak specific Agent Parameters values to improve resolve latency. . Run SQLIO to test disk subsystem throughput . Engage hardware vendor for IO subsystem to improve disk read/write performance

Self-Help resources for this scenario

Below is a list of self-help resources for this scenario.

First Steps in Troubleshooting Replication


Replication Metadata Collection Scripts http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=ReplTalk&ReleaseId=4032

KB article 312292 How to enable replication agents for logging to output files in SQL Server http://blogs.msdn.com/repltalk/archive/2010/03/04/kb-article-312292-how-to-enable-replication-agents-for-logging-to-output-files-in-sql-server.aspx

Divide and Conquer Transactional Replication using Tracer Tokens http://blogs.msdn.com/repltalk/archive/2010/03/11/divide-and-conquer-transactional-replication-using-tracer-tokens.aspx

Transactional Replication Conversations http://blogs.msdn.com/repltalk/archive/2010/02/21/transactional-replication-conversations.aspx (http://technet.microsoft.com/en-us/library/cc732035(WS.10).aspx)

Enhancing Transactional Replication Performance http://msdn.microsoft.com/en-us/library/ms151762(SQL.90).aspx

Replication team blog http://blogs.msdn.com/ReplTalk (http://blogs.msdn.com/clustering)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: