In this blog, we will talk about improving distribution agent throughput by modifying the profile parameter – “SubscriptionStreams”. I will be discussing a few real time scenarios where I implemented the parameter to isolate latency issue between Distributor and Subscriber.

Real Time Scenario

Let’s think about;

• In your environment, you found that there are a few millions of rows that need to be replicated from Distributor to Subscriber(s). When you check out the replication monitor, it says that it’s going to take one or more days to replicate the data at the Subscriber(s). But, the business demand is to sync the data ASAP.
• You have a business requirement to reduces the latency when moving data from Distributor to Subscriber.

First of all, you might be wondering what can cause the latency issue between Distributor to Subscriber. Right?? There could be a bunch of reasons like;

• The rate of incoming transactions from Publisher is too high where the subscriber is not able to handle that transaction fast enough.
• Blocking
• Stale Statistics
• Slow Disk Drives
• Slow Network Bandwidth
• Deadlock……the list goes on and on…

The question is – how would you deal the latency issue between Distributor and Subscriber without re-initializing the replication? That’s where you can utilize the SQL Replication SubscriptionStreams parameter.

What is SubscriptionStreams parameter?

This parameter helps to reduce the latency when moving data from Distributor to Subscriber by using multiple parallel writer threads. It is only applicable for Distribution Agent. It is also known as Distribution Agent SubscriptionStreams parameter. 

Consideration before using the SubscriptionStreams parameter

As a DBA, you need to understand the root of problem and need to test whether SubscriptionStreams is going to help or not. Based on my experience with the parameter, I find this setting is a boon as well as well as a curse. For example;

Boon

• If the incoming transaction rate from Publisher is too high and you feel that one single thread for Distribution agent is not capable enough to catch up all the incoming transactions fast enough, you can consider using the SubscriptionStreams parameter.
• After working with storage team, you figured out that latency is coming from Distributor to Subscriber because of slow disk. You may find enabling SubscriptionStreams parameter helpful.
• You can also consider enabling the parameter, if you find the latency because of network slowness issue.

Curse

• If you find a considerable number of deadlocks are happening at Subscriber and the tables which are involved in the deadlock are also part of incoming replication, enabling the SubscriptionStreams parameter may make the situation worse.
•  If you find blocking at Subscriber, increasing the number of concurrent connections won’t help but might make the situation more bad.

How to configure SubscriptionStreams

• Right-click on the Replication folder and Open Replication Monitor
• In the left pane of Replication monitor window, expand the Publisher and select the Publication.
• On the right pane window, under “All Subscriptions” tab, you will see the list of all the Subscribers of the selected Publication.
• Right Click the Subscriber where you found latency problem and click on the “View Details” option
• A new Window will popup with Distribution Agent session details.
• Click on “Action” in the Menu bar at the top and select “Distribution Agent Job Properties”. This will again popup the Job properties windows for the Distribution Agent.
• Highlight “Steps” in the left pane window followed by highlighting “Run Agent” on the Right pane window, click Edit.
• A new Windows will pop-up , scroll to the very right end of the command section and append this parameter “ -SubscriptionStreams 8” (without quotes)
• Save the settings and restart the Distribution Agent job. A restart of Distribution Agent is required to implement the changes.

Once you enable the SubscritpionStreams parameter successfully on the distributor agent, you will see multiple connections at subscriber side. In my case, I have activated eight streams, you will see 8 connection at subscriber side.

SubscriptionStreams getting RESET to single stream (single thread)

If one of the connections fails to execute or commit at subscriber side, all connections will abort the current batch, and the agent will use a single stream to retry the failed batches. You will have to set it again back to multiple streams. Let’s take the example of above eight connection (SPIDs) – If any one of the SPIDs fails to execute or commit at subscriber side, all connections will abort the current batch, and the agent will use a single stream to retry the failed batches.

To figure out what error caused SubscriptionStreams to reset from multiple streams to single stream, you have to enable  Verbose Log parameter in the distribution agent to get the agent log. Below is parameter which you can directly enable in the distribution agent job;

-output C:\agent1.txt -outputverboselevel 3

Refer the below captured verbose log to have better understanding on resetting of SubscriptionStreams streams.

‘Error converting character data to unicode’, ‘Error converting character data to unicode’, 0xc668770430000080, 0xc668771430000080, 0xc7bf3a0930000080, 20, ‘Error converting character data to unicode’, ‘Error converting character data to unicode’, 0xc668771430000080, 0xc515ecf330000080, 1, 0xd3b330fbd5346766, 0xc7bef61b30000080, ‘Error converting character data to unicode’, 1, ‘Error converting character data to unicode’, 0xa207cbbb30000080, 2017-09-20 15:33:41.890, 0xc7bdc82d30000080, 0xc515ecf330000080, 1, ‘Error converting character data to unicode’, ‘Error converting character data to unicode’, 0xd3b330fbd5346766, 2017-09-20 15:33:41.890, ‘Error converting character data to unicode’, ‘Error converting character data to unicode’, ‘Error converting character data to unicode’, 0xc7bf3a0930000080, 0xc7bf3a0930000080, ‘Error converting character data to unicode’, ‘Error converting character data to unicode’} 2017-10-14 08:45:35.961 Number of subscription streams has been reset from 8 to 1, state 4.

This is one of the advanced settings of replication. Hence I would suggest you to do a thorough testing before implementing this to production.

Happy Learning and your valuable inputs are most welcome!

The post SQL Replication SubscriptionStreams setting appeared first on .