We are noticing an interesting pattern for HADR_SYNC_COMMIT
waits in our environment. We have a three replica; one primary, one sync secondary and one async secondary in a datacenter and we just added three more ASYNC replicas in another datacenter (~2400 miles apart).
Ever since, we have started to notice an enormous increase in HADR_SYNC_COMMIT
waits. When we look at the active sessions, we see a bunch of COMMIT TRANSACTION
queries waiting on the SYNC replica
From the screenshot, we can clearly see there is a jump in HADR_SYNC_COMMIT
wait on June 29, and we eventually dropped 'two' of the three async replica in the remote datacenter sometime in the noon on July 1st. That dropped the wait times considerably along with it.
What we have checked so far – Log send queue, Redo queue, last hardened time and last commit time on the remote replicas. We have continuous bursts of small transactions during the business hours, and therefore the send queues are pretty small at a given timestamp (anywhere between 60KB and 1MB).
The remote replicas are almost in sync, there is very little difference between the last commit time and last hardened time for any individual lsn on the replicas.
The network pipe is 10G and we modified the transmit buffer size from 256 megs to 2 gigs, this was made under the assumption that the network was dropping packets and re-transmitting them; either way that didn’t seem to help much.
So, I’m wondering what does the ASYNC replicas have to do with HADR_SYNC_COMMIT
waits? Shouldn’t the SYNC replica depend alone on this wait type, what am I missing here?