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?