1

I have a central publisher and several subscribers that are all physically located together and on the same internal network - think of a shop with 7 tills whose local DB replicates to the central DB and then back to the other tills so orders can be looked up on any till.

The replication has been working for several years however the performance has been quite slow and varies vastly between each of the end points - the slowest is showing in the replication monitor as 0.42 rows/second and the fastest is 9.52 rows/second with the others being 1, 3, 4, 6 and 8 rows/second.

I've never quite been able to figure out why there is such a range when all of the end points are physically located next to each other and they all have (from what I can determine) the same configuration. We've eliminated any kind of hardware performance and configuration issues so I'm convinced it is something to do with the replication itself but I don't really understand how to troubleshoot this any further.

How can I see what is causing the slow down on the terminal in question?

Update:

This is merge replication where orders are stored locally on the tills, replicated up to the central server and then replicated back down to the other tills so all tills have a copy of all orders.

Tills are running SQLExpress and the replicating central server is SQL Server - all running 2014 versions.

  • 2
    Before digging too deep into replication, have you verified that there isn't a problem with just inserting into the table itself? Run Profiler against the subscriber and verify that inserts/updates aren't taking longer on one box than the other. If it still appears to be replication, can you tell us what version and how replication is setup (push/pull, transactional/merge). Finally, I've seen many strange things with replication and sometimes just removing it and re-creating it causes those issues to go away. – paulbarbin Aug 28 '18 at 13:17
  • @paulbarbin thanks for the suggestion. There aren't any local issues as far as I can see, the orders are taken and stored locally first and then replicated up to the central server and there's no issues we can see with that. The only manifestation is not being able to see the orders from the other tills, currently the least performing replications are over an hour behind! – Gavimoss Aug 28 '18 at 13:36
  • I'd still like to eliminate some variables. One, are we sure there are no extra triggers, indexes or other external factors that just don't allow inserts to happen quickly on those clients? Can you do any other performance test on inserts or updates that would indicate a general slowness? What about quantity of data? Any other characteristics of the slower tills that aren't there in the fast ones? If you have fully exhausted these, then you can start looking into troubleshooting replication. Are the inserts very linear (like exactly .4 per second), or is this just an average? – paulbarbin Aug 29 '18 at 18:15

Your Answer

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

Browse other questions tagged or ask your own question.