0

We're seeing very high waits for HADR_SYNC_COMMIT at times and after some diagnostic work with iperf3 and the like we think there might a network bandwidth problem.

However we're not central sql staff we mostly do database tuning work so I just wanted to try and clarify something that I can't seem to get a definitive answer for on google.

We have two servers (AL and BE) and there are several instances on each. For some one is primary for others it's secondary.

We know that sql uses a single connection for sync and that's how we did the iperf3 tests. At times the bandwidth seemed to drop to zero or near to it.

What we aren't totally sure about is the level this is applied at. Is it instance level so that each instance has it's own sync connection? Or if there's more than one availability group do they each get their own connection?

I hope that's clear. We're still learning about this stuff.

Thanks

Improve this question
2
  • Especially since both servers are under load, another possible factor is disk contention. Are both servers in the same data center or do they need to hit an external connection to talk? If it's all internal, I'm doubtful that you have network saturation there, if it has to go external then I would get your network folks involved to monitor bandwidth there.
    – Jonathan Fite
    Commented Aug 18, 2023 at 14:48
  • Yes HADR_SYNC_COMMIT includes log file write time on the secondary.
    – David Browne - Microsoft
    Commented Aug 18, 2023 at 14:53

1 Answer 1

Reset to default
3

What we aren't totally sure about is the level this is applied at. Is it instance level so that each instance has it's own sync connection?

Yes, there is one connection per instance. There is only allowed one "mirroring" endpoint per instance, all traffic goes through it. You can also see this if you look at the tcp connections for the instance with something like netstat -p tcp -ano. Note that this connection is shared with all items on the same instance and there is no specific QoS (quality of service) for sharing.

Or if there's more than one availability group do they each get their own connection?

There is a single endpoint and a single TCP/IP stream. However, at various levels there are virtual connections to each other. For example if you have two availability groups AG1 and AG2, hosted on the same instance where AG1 is primary and AG2 is a secondary, there will be 1 different incoming (TCP) connection for each secondary replica (or forwarder) for AG1 and there will be 1 outbound (TCP) connection to the mirroring endpoint for the primary of AG2. This same setup with have various "virtual" connections at the replica and database level that are not physical connections but will be communication that occurs over the physical connection.

We're still learning about this stuff.

No worries. If you're interested, I have also written the following items which may be helpful.

Understanding HADR_SYNC_COMMIT

Network Throughput Hysteria

FAQ on AGs

Improve this answer
1
  • Thanks everyone for the responses. This is much clearer now. I wasn't getting any notifications so had no idea anyone had responded.
    – Simon Bradley
    Commented Aug 31, 2023 at 9:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.