SQLServerWiki

“The Only Thing That Is Constant Is Change”

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

Posted by database-wiki on April 11, 2016

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.

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: