Sample Header Ad - 728x90

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: 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
Asked by Ramil R (1 rep)
Feb 17, 2023, 07:39 AM
Last activity: Feb 20, 2023, 07:06 PM