Navigating SQL Replication SubscriptionStreams setting

Rishi Maini | Microsoft SQL Escalation Services

This blog posting discusses the new Distribution Agent SubscriptionStreams parameter.  This option reduces the latency when moving data from Distributor to Subscriber by using multiple parallel writer threads.  For more information on Replication “threads” see Transactional Replication Conversations blog posting.

From SQL Books Online (good place to start)

-SubscriptionStreams [0|1|2|…64]

Is the number of connections allowed per Distribution Agent to apply batches of changes in parallel to a Subscriber, while maintaining many of the transactional characteristics present when using a single thread. For a SQL Server Publisher, a range of values from 1 to 64 is supported. This parameter is only supported when the Publisher and Distributor are running on SQL Server 2005 or later versions. This parameter is not supported or must be 0 for non-SQL Server Subscribers or peer-to-peer subscriptions.

Note: 

If one of the connections fails to execute or commit, all connections will abort the current batch, and the agent will use a single stream to retry the failed batches. Before this retry phase completes, there can be temporary transactional inconsistencies at the Subscriber. After the failed batches are successfully committed, the Subscriber is brought back to a state of transactional consistency.

When you specify a value of 2 or greater for –SubscriptionStreams, the order in which transactions are received at the Subscriber may differ from the order in which they were made at the Publisher. If this behavior causes constraint violations during synchronization, you should use the NOT FOR REPLICATION option to disable the enforcement of constraints during synchronization. For more information, see How to: Control the Behavior of Triggers and Constraints During Synchronization (Replication Transact-SQL Programming).

Factors to consider before jumping into the SubscriptionStreams

As you must have guessed by now, SubscriptionStreams mainly takes care of latency from Distributor to Subscriber, so before you decide to go for SubscriptionStreams make sure that you are indeed running into latency from Distributor to Subscriber. You can either use Tracer Tokens in Replication Monitor or can use Performance Monitor counters like “SQLServer:Replication Dist. – > Dist:Delivery Latency” to have an idea of the level of latency.

Latency from Distributor to Subscriber can be because of number of reasons like :- blocking either at Distributor or at Subscriber, any bottleneck either on Distributor or Subscriber like slow disk drives, slow network bandwidth, stale statistics , bulk transactions coming from Publisher, rate of incoming transactions from Publisher is too high, Triggers or unnecessary Indexes at the Subscribed Database and the list goes on and on.

The DBA needs to take a call and test whether SubscriptionStreams is going to help them or not , for example in case of blocking at Subscriber , increasing the number of concurrent connections won’t help but might make the situation worse. Whereas in situations like the incoming transaction rate from Publisher is too high and you feel that one single thread for Distribution agent is unable to cope up with the incoming load, you can consider increasing the SubscriptionStreams parameter to >=2. It might also help in slow network and slow disk situations. Ideally the Max value for this parameter is 64 but the recommended value (or a good value to start with) is to set it equal to the number of physical processors at the Destination (Subscriber).

How to configure SubscriptionStreams

SubscriptionStreams is one of those parameters which is not visible in Distribution Agent Profile in Replication Monitor.  You’ll need to specifically add this parameter in the Distribution Agent Job command section.  Similar walk through showing how to get to the Agent Job Properties can be found in blog posting Adding OUTPUT for Agent Logging.

Here are steps on how to do this:

1. Open Replication Monitor, expand the Publisher and highlight the Publication in the left pane window.
2. On the right pane window , under “All Subscriptions” section , you will see the list of all the Subscribers to this Publication.
3. Right Click the Subscriber in question and click on “View Details”. A new Window will popup with Distribution Agent session details.
4. On this new Window click on “Action” in the Menu bar at top and select “Distribution Agent Job Properties”, this will again popup the Job properties windows for the Distribution Agent.
5. Highlight “Steps” in the left pane window followed by highlighting “Run Agent” on the Right pane window, click Edit.
6. A new Windows will popup , scroll to the very right end of the command section and append this parameter “ -SubscriptionStreams 6” (without quotes)
7. Save the settings and restart the Distribution Agent job . A restart of Distribution Agent is required to implement the changes.

In the above example SubscriptionStreams is to set to 6 which means that we are looking for 6 parallel connections for Distribution Agent at the Subscriber. You can set this number as per your environment and testing.

How many streams?

Please go through the below link to have more insight to tuning SubscriptionSteams setting.

How to use the SubscriptionStreams parameter of Distribution Agent to test for improved disk subsystem throughput in SQL Server 2005
http://support.microsoft.com/kb/956600/

Is the Distribution Agent really using the parallel connections?

As mentioned in BOL, there is a high possibility that the order in which transactions are received at the Subscriber may differ from the order in which they were made at the Publisher, this could result in an attempt to insert a child row first when the corresponding row in the Parent table is still not replicated. In such scenarios all connections will abort the current batch, and the Distribution Agent will use a single stream to retry the failed batches. Thus how do you know if the Distribution Agent is really using parallel thread or if it has fallen back to single stream.

The below link has the answer for you, a “must read” for all who are interested in using SubscriptionStreams.

How to configure and troubleshoot the SubscriptionStreams parameter of the Distribution Agent in SQL Server 2005
http://support.microsoft.com/kb/953199

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