1

We have a defined and well running transactional replication process. One publisher, one distributor (on separate servers) and several subscribers (pull). All servers have Enterprise Edition.

Latency checked using a canary table. I use a canary table on all subscribers instead of tracer tokens. Don't see any additional latency. There is a great article by Kendra Little showing that canary tables are very good for this measure, since tracer tokens are too patient.

The replication is not for the whole database, only several tables, but the tables are relatively big and there are a lot of inserts.

I added one more subscriber using Standard Edition. It runs well and has no issues except it shows more Latency issues, not huge but it is alerting much more than all other subscribers.

Questions

How can I handle this issue? Is it possible to improve? What should I check? If the server is outperformed on inserts, what should I do to improve it?

Information added in response to comments

The Standard Edition server itself is the same as the other subscribers. All servers are created by DevOps using the same configurations. We have 12 CPU cores on all the subscribers. The Standard Edition subscriber usually performs the same as all other subscribers (Enterprise). Only some times it starts to show increasing delay (30min-1hr) and it takes more than hour to recover.

PLE is 55146 (on Enterprise subscriber it is 178775). The main wait stats on the Standard subscriber are:

  • CXPACKET
  • PAGEIOLATCH_EX
  • PAGEIOLATCH_SH
  • PREEMPTIVE_OS_AUTHENTICATIONOPS
  • IO_COMPLETION
  • LOGBUFFER

The latency is between the distributor and the new subscriber. It usually serves ok and shows no latency as for all other subscribers. But once or twice a day it show latency that at its peak gets to 30min-1hr. I find that it happens when there are thousands of updates coming.

The tables are not partitioned. Everything in replicated tables suits Enterprise & Standard.

  • 1
    I have done what I can to incorporate information from the 21 comments left on this question into the question body. Please feel free to edit the question further to improve on my efforts, or roll it back completely if I made a terrible mess. In future, please ensure all information needed to answer is in the question body since comments are temporary. – Paul White Nov 29 '16 at 13:49
1
+50

Take a 1-2GB file and copy it from Dist to a 'good' subscriber. Note speed/sec and elapsed time. Then do the same to the 'latent/new' subscriber. If much difference you have a hardware/storage/bandwidth issue. Failing that, maybe subscriber db settings are off...small autogrows, etc

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.