3

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?

TT.
  • 15,774
  • 6
  • 47
  • 88
D. Jonker
  • 103
  • 1
  • 7
  • [Bad habit to kick: putting NOLOCK in each query](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/). Effects include: dirty reads, missing rows, reading rows twice, reading multiple versions of the same row, index corruption, read error... – TT. Jan 07 '16 at 09:14
  • 1
    Due to the nature of the program I decided to put NOLOCK in as we can't risk causing any delay on the primary application which is writing to the database. Under normal circumstances I would not do so. Thanks for the advice though :) – D. Jonker Jan 07 '16 at 09:41

1 Answers1

2

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.

D. Jonker
  • 103
  • 1
  • 7
  • You are running in synchronous mode, then the problem would be insufficient network bandwidth if I read that correctly? So a solution would be to switch to asynchronous mode, or to provide a better network connection with sufficient bandwidth? – TT. Jan 08 '16 at 10:39
  • I'm looking at improving the way we read from the table at the moment. Storing all the information needed in a data table and then processing it on C# sharp side to reduce processing on the SQL side. The DBA does not wish to use asynchronous mode as he cannot afford any data loss in the event of a fail over. I will have a chat to him about the bandwidth though, thanks for the suggestion. :) – D. Jonker Jan 08 '16 at 10:59
  • You're welcome. This is all interesting stuff so thanks for digging up this information. Don't forget to check your answer as the solution. – TT. Jan 08 '16 at 11:10
  • How did you ended up with the problem? Have you changed to asynchronous mode or accepted delays caused by read-only queries? – Michael Freidgeim Aug 19 '23 at 02:23