Distribution Agent unable to STREAM when replicating as TSQL

New for SQL 2008 is the ability for the Distribution Agent to push updates to the Subscriber using multiple connections.  This feature, called Subscription Streams, only works when replicating transactions as stored procedures and not TSQL statements.

See: Enhancing Transactional Replication Performance (Use the –SubscriptionStreams parameter for the Distribution Agent.)

Background

The published articles can be configured to replicate INS/UPD/DEL to the Subscribers using standard TSQL statements like “UPDATE SalesOrderDetail SET Price = 2 WHERE OrderId = 43659” or using the default method which is to call a Replication created stored procedures on the subscriber, passing as parameters the column to update and the table’s PrimaryKey.

To confirm which method is selected you can execute the Replication stored procedure sp_browsereplcmds show data queued in the Distribution database.  You can review the SQL BOL for optional parameters.

{CALL [dbo].[sp_MSupd_SalesSalesOrderDetail] (,,,2,,,,,,,43659,1,0×0800)}

You can also script out the publication and review the sp_addarticle statement.  The ‘SQL’ parameters as shown below indicate replication updates as TSQL commands to the subscriber and not the default replication created stored procedures.

@ins_cmd = N’SQL’, @del_cmd = N’SQL’, @upd_cmd = N’SQL’

SubscriptionStreams (hashkey)SubscriptionStreams

The SubscriptionStreams feature only supports replicating using the default replication stored procedures.  If this setting is customized to replicate using TSQL commands, Subscription Streams parameter will be ignored.  To confirm if replication configuration selected supports Streams, you can also query the distribution database MSrepl_commands and check hashkey column.  Value =0 indicates single stream, Values 0-63 indicates Streams are allowed.

SELECT TOP 100 [publisher_database_id]
      ,[type]
      ,[article_id]
      ,[hashkey]  -- > 0 value indicates single stream
FROM [distribution].[dbo].[MSrepl_commands]
ORDER BY [xact_seqno] DESC

In some environment where Distribution Agent continues to drop back to single stream.  This can occur if there are check-constraints, non-clustered indexes, triggers, or other settings blocking multi-connection Distribution Agents from committing parallel updates.  If this occurs, consider creating additional publications allowing each publication to distributed changes via it’s own Distribution Agent.  However don’t break up related table into different publication as you can not guarantee transactions from different publications will be delivered at the same time.  You can get other tips at docs.microsoft article below.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/enhance-transactional-replication-performance