0

There is 2 node AlwaysON AG in synchronous mode. In the morning (09:00 am) we noticed that queue from application side started to grow, in SQL Server there were high HADR_SYNC_COMMIT wait type.

Using the article https://techcommunity.microsoft.com/t5/sql-server-blog/troubleshooting-high-hadr-sync-commit-wait-type-with-always-on/ba-p/385369 we configured Extended Events session on Primary and on Secondary, gathered data for 10 minutes, then changed AlwaysOn AG to asynchronous mode and issue is gone.

Here's what we got in Extended Events session.

Did analysis like in article above.

On primary:

enter image description here

On secondary:

enter image description here

As you see the biggest latency here is on primary in hadr_capture_log_block between mode 2 and 3 ~ 249 ms.

As far as I understand the bottleneck was in "Queue of DbMgrPartner" - it was processing too long. The question is what the root cause ?

Network metrics (Bytes sent, bytes received) in perfmon didn't change after switching to async mode.

One interesting point in perfmon on Primary:

User's image

3 identical lines here: Bytes Sent to Replica/sec, Bytes Sent to Transport/sec and Log Bytes Flushed/sec 10:35 - time when we switch to async

CPU consumption for this period of time enter image description here

Top 5 Waits for periods of 5 minutes: enter image description here

1 Answer 1

1

The question is what the root cause ?

Currently there is insufficient information to make that determination. None of us have your data, but assuming 249ms of time for a single log block (which is highly suspect of being noticed in the first place) took any modicum of time between enqueue and dequeue is generally due to thread execution and locks.

No point getting into the weeds of it, but the basic items to look for more information are at the scheduler health of each scheduler, the processor health, and any long mutex/spinlock times. Generally it's due to a scheduler or set of schedulers having high active lists and switching at times that may be critical. Note that SQL doesn't really switch the context, Windows is in control of this and there are a vast number of things which can preempt a running thread and put it back on - even in the middle of execution with no (Windows) wait reason.

Similar about cpus that are overused during the time, note that 100% cpu usage is not required in order to be a problem. Also note that hyperthreaded cores aren't full execution units.

Lastly we have no idea if this is a VM, cloud compute, bare metal, etc., and that will obviously be bound by the limits and whims of said items.

Response To Waits

With the amount of scheduler yield between 10:31 and 10:36 data points which is when the issue occurred (although the entire 15 minutes posted, really) there is contention for scheduling. This is exactly what I explained above. Additionally, it's in a VM, so you're at the mercy of the hypervisor as well.

3
  • Thank you for your answer Sean! It's VM - AMD EPYC 75F3 32 - Core processor; 30 cores devoted to this VM 480 GB RAM on VM, 410 GB for SQL Server Instance
    – Ramil R
    Feb 20, 2023 at 7:00
  • I edited main post adding information about cpu consumption and top waits
    – Ramil R
    Feb 20, 2023 at 7:14
  • Yeah you have some cpu contention which is causing a problem @RamilR. Feb 20, 2023 at 19:06

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.