I am currently working on an application that continuously queries a database for real time data to be displayed.
In order to have minimal impact on systems which are writing to database, which are essential to the business operation, I am connecting to the Read Only replica in the availability group directly (using the read only replica server name as opposed to Read Only routing via the Always On listener by means of applicationintent=readonly).
Even in doing so we are noticing response time increases on the inserting of data to the primary server.
To my understanding of secondary replicas this should not be the case. I am using NOLOCK hints in the query as well. I am very perplexed by this and do not quite understand what is causing this increase in response times. All I have thought of so far is that SQL is, regardless of the NOLOCK hint, locking the table I am reading from and preventing the synchronous replication to the read only replica, which is in turn locking the primary instances table, which is holding up the insert query.
Is this the case or is there something I am not quite understanding with regard to Always on Read only replicas?
I found this document which I think best describes what could be possible causes of the increases in response times on the primary server.
In general it's a good read for those who are looking into using their AlwaysOn Availability group to distribute the load between their primary and secondary replicas.
for those who don't wish to read the whole document it taught me the following (in my own rough words): Although very unlikely, it is possible that workloads running on the secondary replica can impact the the time taken to send the acknowledgement that the transaction has committed(the replication to the secondary). When using synchronous commit mode the primary waits for this acknowledgement before committing the transaction it is running (an insert for example). So the increase in time for the acknowledgement of the secondary replica causes the primary replica to take longer on the insert.
It is much better explained in the document though, under the 'Impact on Primary Workload' section. And again, if you want to know more, I really suggest you read it.