Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?
Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?


It depends on the sync mode you have used when configuring Availability group - Sync or Async !

On Secondary replica, all the transactions use Snapshot Isolation level ONLY and all locking hints are ignored as well. That's why it's important to test your workload when embracing AlwaysON.

From : Minimizing blocking of REDO thread when running reporting workload on Secondary Replica


  While mapping reporting workload to snapshot isolation eliminates blocking between DML workload as applied by the REDO thread on the secondary replica and the read or reporting workload, it does not eliminate the potential blocking of REDO thread when it is executing a DDL operation.


If using 


Synchronous mode 


Blocking issue on your secondary replica will impact performance of your queries on Primary replica. So a read workload (select) ran on secondary might blocked the redo thread from applying the changes that comes from primary replica. This means that the primary replica has to wait for changes to be applied to all secondary SYNC replica before it commits locally &amp; might end-up in timeouts or blocking or deadlocking. 

The REDO thread can be seen on the Readable Secondary as the DB STARTUP command in sys.dm_exec_requests. If that thread is being blocked, then your read workload on the secondary could be causing an impact on the primary. 

For more details check - Scenario 1:  Blocked REDO due to large query on the secondary replica

Asynchronous mode


The primary does not wait for acknowledgement from secondary. A blocking issue on secondary is just isolated to secondary wherein the redo queue will grow on the secondary until the locks are clear and the redo thread is able to apply the log blocks. This won't affect the primary replica.



Your definition of "real-time or almost real-time" needs more thought keeping in mind the sync method used, the network latency and how busy is primary replica and the log activity that needs to be transported secondary.

SQL Server 2016 has done some major enhancements in AlwaysON realm e.g.  


Improved compression algorithms &amp; Parallel compression of log block data
4x to 5x improvement in log block transport throughput
and many others - It Just Runs Faster !