When troubleshooting SQL 2005/2008 Distribution Agent performance, first determine if the Distribution Agent Latency is from the Reader Thread retrieving rows from the DistributionDB or the Writer Thread writing to the subscriber.  Modify the Agent startup parameters to include the -OUTPUT with agent stats.

 

************************ STATISTICS SINCE AGENT STARTED ***********************
05-01-2009 17:35:54

Total Run Time (ms) : 18828  Total Work Time  : 14110
Total Num Trans     : 52  Num Trans/Sec    : 3.69
Total Num Cmds      : 52  Num Cmds/Sec     : 3.69
Writer Thread Stats  Time Spent on Exec        : 12063
  Time Spent on Commits (ms): 422  Commits/Sec         : 0.14
  Time to Apply Cmds (ms)   : 14110  Cmds/Sec            : 3.69
  Time Cmd Queue Empty (ms) : 671  Empty Q Waits > 10ms: 2
Reader Thread Stats
  Calls to Retrieve Cmds    : 2
  Time to Retrieve Cmds (ms): 92  Cmds/Sec            : 565.22
  Time Cmd Queue Full (ms)  : 5486  Full Q Waits > 10ms : 3
*******************************************************************************

 

In this output clearly the READS completes very fast while WRITES are consuming most of the time contributing to the high latency.

 

What to check:

 

1) Obtain the create publication script and check ALL articles to see if the SQL statements are being sent as batch of 'parameters' statements.  The status < 16 indicates 'parameters' batching is not enabled.  Default value is 16.

 

    exec sp_addarticle @publication = ... @status = 24

or

   select name, status from sysarticles

 

Article status can be updated on-demand using the following Replication stored procedure.  This should be executed on all articles.

 

    EXEC sp_changearticle @publication = N'<pub name>', @article = N'<article name>',

                                      @property = 'status',  @value = 'parameters'

 

2) Capture Profile RPC:COMPLETE events on the Subscriber.  Look for the time difference between StartTime and EndTime and/or Duration.  Statements should be completing around 100ms.  Longer execution times for single row update/inserts may indicate blocking on the Subcriber or user-defined triggers on the subscriber tables contributing to the high durations.

 

The default value is 16