We had a business requirement to add a cloud-based asynchronous replica to an on-premises SQL Server Always On Availability Group. After adding a high-volume database to the Availability Group, we noticed that the log send queue started growing rapidly.
Our initial suspicion was that network latency between the on-premises primary replica and the cloud replica was contributing to the problem. However, before requesting a network upgrade, we needed a way to prove that network performance was actually affecting data movement.
One useful approach is to use the Always On data movement Extended Events trace described in Microsoft's article:
Troubleshooting data movement latency between synchronous-commit AlwaysOn Availability Groups
Although Microsoft's example focuses on a synchronous-commit replica, the same data movement events can be used to investigate an asynchronous replica, with some important differences.
The First Symptom: A Growing Send Queue
The first indication was that the log send queue was significantly larger than the redo queue.
For example:
SELECT
ar.replica_server_name AS ReplicaName,
DB_NAME(drs.database_id) AS DatabaseName,
drs.log_send_queue_size AS LogSendQueueKB,
drs.redo_queue_size AS RedoQueueKB
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar
ON drs.replica_id = ar.replica_id
WHERE DB_NAME(drs.database_id) = 'DB1';The result looked like this:
| ReplicaName | DatabaseName | LogSendQueueKB | RedoQueueKB |
|---|---|---|---|
| SQL1 | DB1 | 79,604,352 | 88 |
The important observation is the difference between the two queues. The send queue was approximately 76 GB, while the redo queue was only 88 KB.
This suggested that the secondary was not primarily struggling to redo the log it had already received. Instead, a significant amount of log was accumulating before it could be delivered to the secondary.
That made network throughput or latency a strong candidate for further investigation.
Capturing the Data Movement Trace
The next step was to capture an Always On data movement trace on both the primary and secondary replicas.
It is important to run the trace for approximately the same period on both servers so that the events can be correlated.
In our case, we captured the trace for 2 minutes and 30 seconds.
IF EXISTS
(
SELECT *
FROM sys.server_event_sessions
WHERE name = 'AlwaysOn_Data_Movement_Tracing'
)
BEGIN
DROP EVENT SESSION [AlwaysOn_Data_Movement_Tracing]
ON SERVER;
END
GO
CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
ADD EVENT sqlserver.hadr_apply_log_block,
ADD EVENT sqlserver.hadr_capture_filestream_wait,
ADD EVENT sqlserver.hadr_capture_log_block,
ADD EVENT sqlserver.hadr_capture_vlfheader,
ADD EVENT sqlserver.hadr_db_commit_mgr_harden,
ADD EVENT sqlserver.hadr_log_block_compression,
ADD EVENT sqlserver.hadr_log_block_decompression,
ADD EVENT sqlserver.hadr_log_block_group_commit,
ADD EVENT sqlserver.hadr_log_block_send_complete,
ADD EVENT sqlserver.hadr_lsn_send_complete,
ADD EVENT sqlserver.hadr_receive_harden_lsn_message,
ADD EVENT sqlserver.hadr_send_harden_lsn_message,
ADD EVENT sqlserver.hadr_database_flow_control_action,
ADD EVENT sqlserver.hadr_transport_flow_control_action,
ADD EVENT ucs.ucs_connection_flow_control,
ADD EVENT sqlserver.hadr_transport_receive_log_block_message,
ADD EVENT sqlserver.log_block_pushed_to_logpool,
ADD EVENT sqlserver.log_flush_complete,
ADD EVENT sqlserver.recovery_unit_harden_log_timestamps
ADD TARGET package0.event_file
(
SET filename = N'E:\AlwaysOn_Data_Movement_Tracing.xel',
max_file_size = (500),
max_rollover_files = (4)
)
WITH
(
MAX_MEMORY = 4096 KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS,
MAX_EVENT_SIZE = 0 KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = ON
);
GO
ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing]
ON SERVER STATE = START;
WAITFOR DELAY '00:02:30';
ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing]
ON SERVER STATE = STOP;
GO.xel files.
Finding a Log Block to Correlate
Once the trace has been collected, we need to identify the database in the trace.
On the secondary replica, run:
SELECT group_database_id
FROM sys.availability_databases_cluster
WHERE database_name = 'DB1';In our example, the result was:
group_database_id
------------------------------------
3F5E2823-002E-46FD-A971-869ED3892B27We can use this value to locate events associated with DB1 in the Extended Events output.
For example, the secondary trace contained events like these:
| name | timestamp | database_replica_id | mode | log_block_id |
|---|---|---|---|---|
hadr_transport_receive_log_block_message |
2026-08-04 09:20:17.2059851 | 3F5E2823-002E-46FD-A971-869ED3892B27 | 2 | 28346698302255272 |
hadr_apply_log_block |
2026-08-04 09:20:17.2060235 | 3F5E2823-002E-46FD-A971-869ED3892B27 | 2 | 28346698302255032 |
hadr_transport_receive_log_block_message |
2026-08-04 09:20:17.2060465 | 3F5E2823-002E-46FD-A971-869ED3892B27 | 1 | 28346698302255392 |
hadr_apply_log_block |
2026-08-04 09:20:17.2060554 | 3F5E2823-002E-46FD-A971-869ED3892B27 | 2 | 28346698302255152 |
hadr_transport_receive_log_block_message |
2026-08-04 09:20:17.2060730 | 3F5E2823-002E-46FD-A971-869ED3892B27 | 1 | 28346698302255512 |
hadr_transport_receive_log_block_message |
2026-08-04 09:20:17.2060836 | 3F5E2823-002E-46FD-A971-869ED3892B27 | 2 | 28346698302255392 |
hadr_apply_log_block |
2026-08-04 09:20:17.2060849 | 3F5E2823-002E-46FD-A971-869ED3892B27 | 2 | 28346698302255272 |
Pick a log_block_id, preferably one toward the latter part of the trace. This increases the chance that the same log block was captured in both the primary and secondary traces.
For this example, we selected:
28346698302255392Searching for the Log Block on the Secondary
Searching for that log block ID on the secondary produced the following results:
| name | timestamp | database_replica_id | mode | log_block_id |
|---|---|---|---|---|
hadr_transport_receive_log_block_message |
2026-08-04 09:20:17.2060465 | 3F5E2823-002E-46FD-A971-869ED3892B27 | 1 | 28346698302255392 |
hadr_transport_receive_log_block_message |
2026-08-04 09:20:17.2060836 | 3F5E2823-002E-46FD-A971-869ED3892B27 | 2 | 28346698302255392 |
hadr_log_block_decompression |
2026-08-04 09:20:17.2061251 | NULL | NULL | 28346698302255392 |
hadr_log_block_decompression |
2026-08-04 09:20:17.2061274 | NULL | NULL | 28346698302255392 |
hadr_apply_log_block |
2026-08-04 09:20:17.2061456 | 3F5E2823-002E-46FD-A971-869ED3892B27 | 2 | 28346698302255392 |
log_block_pushed_to_logpool |
2026-08-04 09:20:17.2063843 | NULL | NULL | 28346698302255392 |
log_flush_complete |
2026-08-04 09:20:17.2068296 | NULL | NULL | 28346698302255392 |
Searching for the Same Log Block on the Primary
Next, search the primary trace for the same log_block_id:
28346698302255392The relevant events were:
| name | timestamp | database_replica_id | availability_replica_id | log_block_id |
|---|---|---|---|---|
hadr_capture_log_block |
2026-08-04 09:19:07.1730670 | 3F5E2823-002E-46FD-A971-869ED3892B27 | D91DF70A-8226-4022-93F6-12F4D5B77699 | 28346698302255392 |
hadr_capture_filestream_wait |
2026-08-04 09:19:07.1730674 | 3F5E2823-002E-46FD-A971-869ED3892B27 | D91DF70A-8226-4022-93F6-12F4D5B77699 | 28346698302255392 |
hadr_capture_log_block |
2026-08-04 09:19:07.1730690 | 3F5E2823-002E-46FD-A971-869ED3892B27 | D91DF70A-8226-4022-93F6-12F4D5B77699 | 28346698302255392 |
hadr_capture_log_block |
2026-08-04 09:20:16.6305401 | 3F5E2823-002E-46FD-A971-869ED3892B27 | D91DF70A-8226-4022-93F6-12F4D5B77699 | 28346698302255392 |
hadr_capture_log_block |
2026-08-04 09:20:16.6305465 | 3F5E2823-002E-46FD-A971-869ED3892B27 | D91DF70A-8226-4022-93F6-12F4D5B77699 | 28346698302255392 |
hadr_log_block_compression |
2026-08-04 09:20:16.6307262 | NULL | D91DF70A-8226-4022-93F6-12F4D5B77699 | 28346698302255392 |
hadr_log_block_send_complete |
2026-08-04 09:20:16.9981083 | NULL | NULL | 28346698302255392 |
The darker gray row is the key event we need from the primary replica: hadr_log_block_send_complete.
Asynchronous Replicas Have Some Important Differences
One important detail is that the events captured for an asynchronous replica differ slightly from those shown in Microsoft's example for a synchronous replica.
With asynchronous replication, the primary compresses the log blocks before sending them, and the secondary decompresses them after receiving them.
As a result, we can see:
hadr_log_block_compressionon the primaryhadr_log_block_decompressionon the secondary
There is also an important difference in how the primary waits for the secondary.
With an asynchronous replica, the primary does not wait for the secondary to harden the log block before continuing. Therefore, we should not expect to see the same hadr_receive_harden_lsn_message round-trip used in the synchronous-commit example.
This makes the primary-to-secondary portion of the data movement particularly useful when investigating network latency for an asynchronous replica.
The Events We Need to Calculate Network Latency
The two events we are interested in are:
Primary Replica
hadr_log_block_send_complete
2026-08-04 09:20:16.9981083Secondary Replica
hadr_transport_receive_log_block_message
2026-08-04 09:20:17.2060465Both events correspond to the same log block:
28346698302255392Calculating the Network Latency
We can calculate the difference between these timestamps using DATEDIFF:
SELECT DATEDIFF(
millisecond,
'2026-08-04 09:20:16.9981083',
'2026-08-04 09:20:17.2060465'
);The result is approximately: 208
In other words, the elapsed time between the primary reporting the log block as sent and the secondary reporting that it received the log block was approximately 208 milliseconds.
Conclusion
This gave us a much stronger data point than simply saying, "the network seems slow."
We were able to correlate the same log_block_id across the primary and secondary replicas and measure the elapsed time between the hadr_log_block_send_complete event on the primary and the hadr_transport_receive_log_block_message event on the secondary.
In this example, that measurement was approximately 208 milliseconds.
Combined with the rapidly growing send queue and relatively small redo queue, the trace provided evidence that network performance was a significant factor in the asynchronous replica's ability to keep up with the primary.
That data gave us the evidence we needed to justify a network upgrade rather than treating the problem as a general SQL Server performance issue.