SQL Server – Transaction Rate Reduction When Mirror Synchronized

mirroringsql servertransaction-log

I'm currently managing a database of ~ 40 giga in a HA/mirrored set up with primary, mirror and witness nodes.

When the system is fully synchronized i noticed a decrease in rate of transactions which has a negative impact on my production environment (general slowness and UI latency).
When the mirror is temporarily suspended everything works as it should.
Principal and mirror are built on the same server model, same OS(SQL server 2012) and same hardware specs.

Is there anything i should check to match the performance when the mirror is suspended?

Best Answer

There are two modes for database mirroring:

  • High safety (synchronous) - every delete/update/insert (DUI) operation is written to the primary's transaction log, sent across the network to the mirror, written to the mirror's transaction log, and then confirmation is sent back to the primary. Transactions are inherently slower, but in a perfect world, you won't lose data when you fail over from the primary to the mirror.
  • High performance (asynchronous) - DUI operations are only written to the primary's transaction log, then the transaction is immediately committed. Later, the primary sends transactions to the mirror - but that traffic doesn't slow down your transactions. However, if you fail over, you can lose data.

The key is in the names: high safety is safe (but slow), and high performance is faster (but less safe.)

You've noticed that when you suspend the mirror, things get faster - that's because your mirroring is set up with high safety.

If your business is willing to lose data when the primary fails, then you can keep doing mirroring, but switch it from high safety to high performance. (You can change this at the database level at any time.)

If your business is NOT willing to lose data, then it's time to start troubleshooting which layer of mirroring is causing you problems:

  • Slow log file writes on the mirror, or
  • Slow network connection between the primary and the mirror, or
  • Lots of tiny transactions in the app rather than doing larger batches

Troubleshooting that is kinda beyond the scope of what I can do in an answer here though.

Related Question