1

We have an application that will generate a lot of insert/update transactions per second.

Recently, we've moved the application to a new server and since then, the performance deteriorated significantly (about 3x slower). I know it could explained by many things but one of the main difference between the 2 servers is the SQL Server replication that was added to the new server.

My comprehension of the replication process is that the subscriber database will try to catch up with the publisher by using it's log file. A slow latency would certainly affect the time required by the subscriber to synch back, but what about the publisher? I don't know what are the exact replication configurations but is there scenarios where a publisher's performance would affected by the replication process?

5
  • 1
    You are a bit confused about replication. you can get an overview of the basics here :- docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/… Yes - adding transactional replication can slow down performance on the publisher. Mar 26 at 15:02
  • 1
    Aside from what Stephen said, if I understand you correctly, the new server had Replication setup, but what is it replicating?...is it replicating the database of the old server that the application was using, and now the application uses that replicated database on the new server? And if so, in what ways does the application use the replicated database (is it writing to it or just reading from it)? If the replicated database is completely unrelated to your application then the performance issue you're seeing is not related to replication.
    – J.D.
    Mar 26 at 15:22
  • My application is only dealing with the publisher DB and will never use the replicated DB. So if I understand correctly, the Log Reader Agent wouldn't have any impact the transactions made on the publisher DB? Mar 26 at 15:59
  • @The_Black_Smurf So the new server is where the Publisher database is?
    – J.D.
    Mar 26 at 18:57
  • Yes, exactly. The old server is offline and the subscriber database is on a third SQL server. And just to clarify, there was no replication at all on the old server. Mar 26 at 19:03
2

While it's theoretically possible for replication on the Publishing database to be affecting your application's write performance to that database (dependent on many factors) it's less likely that's your issue.

When you change servers, even if it's an exact replica server to the same specs (hardware and software) and same exact version of SQL Server, there's still going to be performance affecting differences such as statistics that will need to be calculated (and potentially change on recalculation) on the new server.

One way it's theoretically possible is if your Distributor lives on the Publisher's server, and is causing resource contention, but this is highly unlikely to be the case. Depending on which kind of replication you're using, there may be other applicable ways it can affect performance as well. For example, Snapshot Replication is more resource intensive than Transactional Replication when it's creates a snapshot (since it's recreating everything, not just the differentials, and locks the table the entire time it's snapshotting it), and therefore isn't meant to be used for as real-time of data synchronizations as Transactional Replication is meant for. If you were using Snapshot Replication and had it scheduled very frequently (e.g. every 5 minutes) then I could see that being more of a potential problem.

But most likely your issue is arising from somewhere else. I'd recommend comparing the old execution plan (if possible) to the new one, and comparing the Time and I/O statistics to try to trace your issue for your application's slow queries. You'll more likely find the root cause going that route.

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.