1

One of our publishers is subscribed to by three subscribers, each subscriber is hosted on its own physical server. Two of those subscribers are 2008R2 and the third 2012. The 2012 subscriber is experiencing latency daily during peak times.

After running some extended event tracing for a couple of days I can see that the latency co-insides with slow sp_MSget_repl_commands execution times at the distributor, and more specifically the final select query found at line 724 "-- Mini join along. Only agent_id and article_id columns in dbo.MSsubscriptions". The execution time for this statement can run into >= 20 minutes.

Does the distributor reader thread wait for the entire execution of sp_MSget_repl_commands to complete before starting to write replicated commands to the command buffers? Or does it start writing replicated commands to the command buffers as soon as they are returned by the SP? I see that the problematic statement is hinted with OPTION (FAST 1) which might suggest that this is the case. If the reader thread does wait for sp_MSget_repl_commands to complete before starting to write replicated commands to the command buffer then that might explain the latency, otherwise I am unsure. Is there anything else obvious I can monitor?

The MSdistribution_history.comments suggest that the holdup is at the subscriber. However no replicated subscriber stored procedure are completing in >= 100 ms and there is no obvious physical server resource contention. I’ve ruled out the network because there are also other subscriber databases on the same physical server being populated across the same network which are not experiencing latency.

  • is this push or pull repication? – rottengeek Jan 14 '15 at 13:56
  • All subscribers are pull. The distributor is remote on its own box (although there is definitely memory pressure occurring at the distributor server) – Pixelated Jan 14 '15 at 14:01
  • is this merge replication? Pull is going to see the bulk of the load on the subscriber. Make sure the Replication jobs are enabled (for some reason, I've seen systems where they aren't) "Distribution clean up: distribution" and "Expired subscription clean up" – rottengeek Jan 14 '15 at 14:26
  • To answer your original question, once a batch of changes is committed to the pulisher, those commands go to the distributor. They stay in the distributor until they are written to all subscibers. I've seen this kind of latency when there is a HUGE batch of modifications made on the publisher (i.e., someone updates millions of rows on a published table) – rottengeek Jan 14 '15 at 14:45
  • Sorry, transactional replication. Just checked and can confirm that both jobs are active. The latency also builds with a high transaction /sec, commands/ sec count which makes sense. However I dont know why the latency is only occurring for one subscriber. I'm especially keen to get to the bottom of at what point does the reader thread of the distributor start pulling commands from the dist. End of sp_MSget_repl_commands execution or as soon as the rows start to be returned. – Pixelated Jan 14 '15 at 14:46
1

To answer my own question to an extent. Commands appear to be written to the command buffer as soon as sp_MSget_repl_commands makes them available. If the writer thread is unable to write the command to the subscriber then sp_MSget_repl_commands spins until the command has been written. Once written the writer notifies the reader and frees one of the two command buffers for the next batch of commands. This explains the long execution of sp_MSget_repl_commands.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.