Analyze Synchronous Commit Impact on High Commit Rate Workloads

Recently the SQL Server Always On Support team at Microsoft CSS published a blog on analyzing the performance impact of Synchronous Commit AG's with workloads that perform a large number of small transactions. For Synchronous AG's every transaction commit needs to be hardened on the transaction log of the secondary replica and the hardening has to be acknowledged back to the primary before the transaction can committed on the Primary replica.

The network round trip time between the primary and secondary replicas has considerable impact on the log hardening time and subsequently on the transaction commit time. In scenarios where a large number of small transactions (e.g. singleton iterative inserts) are performed on the primary replica, the log transport time (capture, send, harden and acknowledge) can significantly impact overall performance. If the application allows it, grouping these transactions in single transaction would provide significant performance benefits.

Sourabh Agarwal
SQL Server Tiger Team Twitter | LinkedIn
Follow us on Twitter: @mssqltiger | Team Blog: Aka.ms/sqlserverteam