Latency with hadr_capture_log_block in SQL Server AlwaysOn AG
0
votes
1
answer
376
views
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:
On secondary:
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:
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
Top 5 Waits for periods of 5 minutes:





Asked by Ramil R
(1 rep)
Feb 17, 2023, 07:39 AM
Last activity: Feb 20, 2023, 07:06 PM
Last activity: Feb 20, 2023, 07:06 PM