Does read-only activity on synchronous secondary replica slow down primary replica in Availability Group?

Question

Suppose that we have an Availability Group configuration (SQL Server 2017) where primary and synchronous secondary do not share any resources (each replica have dedicated CPU, memory, disk, etc.)

Will read-only activity on synchronous secondary have performace impact on primary replica ?

The reason I ask because different sources tell different (contradictory) things:

Some sources say it has impact and not recommended to offload read-only activity to sync secondary (recommend only offload to asynchronous replica), and others tell that is best practice to offload reporting load on secondary even if it is sychronous

What do you think on this ?

Answer 1

usually, there is no impact on primary replica in performance point of view as the resource utilization is completely on secondary replica. i.e. (CPU, Memory, IO), and read operation would takes place on secondary copy of the database.

I believe, your assumption of performance impact considering the log processing workers, but that (AG worker connections) would anyway constantly active (which can be identified with select * from sys.dm_exec_connections where protocol_type = 'Database Mirroring' ) on primary replica regardless weather the secondary read-only operation or not. If it's really a concern you can use dedicated Ethernet for Database mirroring end-pints.

In summary, there is no performance impact but we should consider about Data Latency as the active transactions from Primary replica cannot be available in Secondary replica's select query until and unless the transaction committed by Primary replica and secondary replica has processed particular transaction.