AlwaysOn reads on readonly replica and latency

Question

Hi all,

On an sqlserver2016 alwayson replica in asynchronous mode with readonly activated, If I have a long query (several minutes) selecting datas, are the logs still applied or is it waiting the end of the query to apply the new logs and keep data consistency during the query ?

Regards,

BeGin

Answer 1

For DML (INSERT, UPDATE, DELETE, MERGE) log is continuously applied, and isolation levels are remapped to row-versioning-based isolation levels on readable secondaries:

Read-only workloads for disk-based tables use row versioning to remove blocking contention on the secondary databases. All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention.

Offload read-only workload to secondary replica of an Always On availability group

However the system catalog views can never be read using row-versioning, so the application of DDL (ALTER TABLE, TRUNCATE TABLE, DROP TABLE, etc) changes to the secondary by the REDO thread still requires an Exclusive Modification Schema Lock (Sch-M) has to be coordinated with the readers who all acquire Schema Stability Locks (Sch-S). So long-running queries on the secondary can block the redo thread if DDL changes are pending to an object involved in the long-running queries.

See

On the secondary replica, the read-only queries acquire schema stability (Sch-S) locks. These Sch-S locks can block the redo thread from acquiring schema modification (Sch-M) locks to make any DDL changes. A blocked redo thread cannot apply log records until it is unblocked.

Determine why changes from primary replica are not reflected on secondary replica for an Always On availability group - Another reporting workload blocks the redo thread from running

Answer 2

That would depend on your isolation level.

Please see:
https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15

Answer 3

If my understanding is correct, logs are being applied all the time. The query will work against the version store, so you will get a consistent view of your query.

If my thinking is correct, this suggests that the isolation levels REPEATABLE READ and SERIALIZE would be possible to use on a read-only secondary.

Answer 4

Hi all,

Thanks for the answer
It seems that the issue might come from DDL running on my primary. It probably creates an io storm and sets some sch-s locks has stated by @davidbaxterbrowne.

I "just" have to find a way to find a schedule where these workloads won't be concurrent.

Regards,

--
Begin