The distribution agent may experience high latency when you configure transactional replication with articles that are configured not to replicate changes as parameterized statements.

Symptoms

In Microsoft SQL Server 2005 or Microsoft SQL Server 2008, you set up a transactional replication.

• The transactional replication contains one or more articles.
• For one or more of the articles you specify a non default value for the @status parameter (default is either 16 or 24) of the sp_addarticle stored procedure. Or, you specify the value for the @ins_cmd, @del_cmd and @upd_cmd parameter of sp_addarticle stored procedure to SQL

In this scenario, you may find that the Distribution Agent will exhibit high latency when replicating data to the subscriber. Additionally you many notice high values for NETWORKIO wait type on the SPID corresponding to the Distribution Agent process on the distributor.

Note: You can confirm the above symptom by generating the script for your publication and by looking at the value of @status parameter for procedure sp_addarticle for each of the articles in the publication. If this value is less than 16, then you are not using parameterized statements for propagating changes to the subscriber for the specified article and hence this article applies in your scenario.

Cause

Replicating commands without using parameterized statements causes each command in a transaction to be sent individually via RPC. Thus when you have a transaction with multiple commands, distribution agent will require multiple round trips to replicate the transaction to the subscriber thereby increasing the latency. On the other hand, when you use parameterized statements (the default setting), distribution agent groups multiple commands in a single transaction into one or more batches and replicates them to the subscriber thereby reducing network round trips and latency.

Note: In a SQL profiler trace a batch would show up as one pair of RPC Starting and RPC completed event.

Resolution

To resolve the problem, change affected article’s status property to “parameters” using sp_changearticle stored procedure. This stored procedure should be executed at the Publisher on the publication database.

The syntax is as follows:

Exec sp_changearticle @publication='<PublicationName>’, @article='<ArticleName>’,@property=’status’,@value=’parameters’

Note: Running the above procedure does not cause or require re-initialization of the subscription.