Verify Distribution Agent Commands are being BATCHED

“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. 


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. 


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’

Comments (2)

  1. What about @status = 24? According to BOL this “Includes the column name in INSERT statements and uses parameterized statements.”

     >>>>>>>STATUS = 24 will batch statements.  You’ll encounter problem when STATUS=0 or STATUS = 8.


  2. eric says:

    Aaron @status = 24 is batched so your good.