Two publications causing blocking on the subscriber using indexed view

Question

Post reply

Two publications causing blocking on the subscriber using indexed view

Roust_m

SSCoach

Points: 17410
More actions
March 26, 2012 at 5:57 pm

#253725

Hi,
I have three publications: A, A_filter and B. A and B have dozens of tables, A_filter has only three tables. A and A_filter have tables that belong to and indexed view on one of the subscribers (Server1). When I have a large update on the publisher I see a lot of blocking on the subscriber on the indexed view.
Is there any way to fix this?
If I have to put the two tables into the same publication I will have to publish almost the entire database twice, as tables from publication A have to go to two subscribers: Server1 and Server2. A_filter has to go to Server1 only, but the three tables have to go to Server2 unfiltered, so I've included them into publication B. And finally the publication B has to go to Server2 only.
Any ideas?
Thanks.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

The only option I can think of that can help is Snapshot Isolation. I cant think of anything else.

-Roy

Roust_m SSCoach Points: 17410 More actions · Answer 2

Roy Ernest (3/26/2012)
The only option I can think of that can help is Snapshot Isolation. I cant think of anything else.

Where should I use Snapshot isolation? The SPs that access the subscriber use (nolock), so they are not affected. It is only the two distiribution jobs, that block each other. How do you put snapshot isolation on the distriburion agent jobs?

Roust_m SSCoach Points: 17410 More actions · Answer 3

I tried playing with

-CommitBatchSize

-CommitBatchThreshold

and

-ReadBatchSize

-ReadBatchThreshold

for distribution and log reader agents respectively and it seemed to had helped, but the replication looks slower.

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

You mention problems when there is a large update on the publisher. Is there any way you can reduce the size of the update transactions there (i.e. more transactions, but fewer updates in each transaction)?

Doing that, and reducing the CommitBatchSize should help to reduce blocking at the subscriber.

As you've seen, reducing CommitBatchSize will make replication slightly slower because all the "preamble and postamble" associated with a replication transaction has to be repeated more often.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

Roust_m (3/26/2012)
Roy Ernest (3/26/2012)
The only option I can think of that can help is Snapshot Isolation. I cant think of anything else.
Where should I use Snapshot isolation? The SPs that access the subscriber use (nolock), so they are not affected. It is only the two distiribution jobs, that block each other. How do you put snapshot isolation on the distriburion agent jobs?

What I was basically suggesting was to set up Row Level version for your replicated DB. What you will gain is you will have less blocking. Take a look at this article that helps you understand how to set up RLV[/url].

-Roy