Tuesday 18 March 2014

SQL Server Transactional Replication Subscription Streams not being used

I was recently looking at increasing the performance of Transactional Replication across a very high latency line (plenty of bandwidth). I had exhausted all of the agent parameter tweaks so decided to look at subscription streams on the subscriber. There's a bunch of good articles out there describing this, not least this article from SQL Server Advisory team which describes the relative benefits (and dangers) of using subscription streams.

So I gave it a go. If you set the -VerboseHistoryLevel to 2 you will get a useful statistics report periodically. Upon checking this I was pleased to see 16 separate threads, so my set up was right. However, when running tests only the first thread was ever used. Looking at the BOL it says :


Note Note
Subscriptionstreams do not work for articles configured to deliver Transact-SQL. To use subscriptionstreams, configure articles to deliver stored procedure calls instead.


Fine, I'm replicating Stored Procedures calls (rather than table articles) so this should work.
Well no, my bad, what that note means is the Standard procedures i.e. SCALL, XCALL, MCALL routines. Not replicated Stored Procedures articles. Simple misunderstanding that made all the difference.

Changed replication to be Table Article based, and behold 16 threads of goodness and over 10 times increase of performance.

No comments: