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?
Best Answer
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.