Someone told me that running queries on a readable secondary in an AG can slow down (or even block) the primary (no matter if it's in sync or async mode).

I do not understand how this is possible. I'll try to explain why I think this is not possible.

  • Sync mode:
    Log records are sent to the secondary and when those records are hardened on the secondary (written to log file) an ACK is sent to the primary and the transaction is committed on the primary.
    If there is some latency on sending the ACK this can affect the primary but I think this can't be caused by a query running on the secondary.
  • Async mode:
    Log records are sent to the secondary and the primary isn't waiting for an ACK from the secondary, it is just 'fire and forget'.

What can happen is that the REDO-thread is blocked by a long running query => changes can't be applied to the secondary but I think this doesn't affect the performance on the primary.

Is my assumption correct or am I missing something?

  • 1
    The unit of transmission is a log block, not log records - fyi. the main slowdown comes in when using sync and overloading the secondary with CPU or IO so that it takes much longer to harden the log blocks or can't send status messages in a timely manner. Mar 17 '17 at 12:07

In Simple terms..

Readers won't be blocked by DML operations,but there is a chance of blocking with DDL operations..

This not blocking is achieved by mapping isolation levels to snapshot isolation levels.As a result of this,there will be row versioning over head,but there won't be any blocking by DML(Insert/Update/Delete)..

See below for more details on how Isolation levels are mapped in various cases

enter image description here

What can happen is that the REDO-thread is blocked by a long running query => changes can't be applied to the secondary

When there is a blocking on secondary due to lock conflicts (thread trying to apply DDL to a page generated on primary ) and redo thread is blocked.The only impact i could see is log growth (depending on synchronous commit or asynchronous commit). But there will be blocking irrespective of sync model you choose.

SQL team, even tried with a design option of killing read only query on secondary automatically,when there is blocking ,but went against the option..

Below is the explanation from Sunil Agarwal on the same

When we were designing this feature, we thought about killing the redo work -- sorry, killing the reporting work automatically. As soon as redo is blocked, we'll say, okay, I'm going to kill the read workload, and the redo moves forward. The problem was if you were running a reporting workload that is very critical for now or your reporting workload was taking half an hour to run and you were almost like 39 minutes done and the blocking happens and if you kill it, it's not a good user story.So what we have done is we have exposed the right knobs and we let the customer decide what to do if the redo thread gets blocked.

so you will have to monitor Xevents to know about blockings and take decision

References used:
AlwaysOn Solution Guide: Offloading Read-Only Workloads to Secondary Replicas
AlwaysOn: Impact of mapping reporting workload on Readable Secondary to Snapshot Isolation

  • So the performance of the primary will never be affected due to a query running on the secondary? Only the REDO thread on the secondary can be blocked. Mar 20 '17 at 8:24
  • @FrederikVanderhaegen: when you run queries are secondary,there will be a performance hit,if those are resource intensive queries,but not in the context of locking/blocking Mar 20 '17 at 8:48

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.