“Repl Talk“

by Chris Skorlinski
Microsoft SQL Server Escalation Services

When you configuration SQL Transactional Replication you can specify if the Distribution Agent should use TSQL or Stored Procedures to move the data to the subscriber.  The default is Stored Procedure. 

image

If you select as INSERT/UPDATE/DELETE SQL statements the commands are not BATCHED to the subscriber resulting in slower throughput and increase in Transactional Replication Latency.

To verify all articles are set to replicate as BATCHED Stored Procedures, generated the Create Publication script then examine the STATUS setting for every article. 

image

Examine the @status.  Any value <16 (0 or 8) indicates article is being replicated using TSQL and will not be batched. If even one article is not set to BATCH, then none of the articles will be BATCHED when applying changes to the Subscriber.

exec sp_addarticle @publication = . . . @status = 8

To change to BATCHED Stored Procedure calls execute the command below.

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

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

For example:

     EXEC sp_changearticle @publication = N'PeerATran', @article = N'Customer', @property = 'status', @value = 'parameters'