0

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 ?

  • 2
    As with all things SQL Server, there is seldom a simple yes or no answer; "it depends". Assuming no shared resources, the primary will be slowed by the sync replica if the read-only activity is significant enough to add commit latency. – Dan Guzman Jul 14 at 12:42
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.