Read-only replica , usage of tempdb as version store

Question

Once the AG database is added to a AG , and the replica are set to allow read-only secondary . Once it set , actually the Update/delete on primary database will induce a copy in version store on tempdb on 2nd replica .

will this induce extra over head on the Primary database transaction ? Longer statement elapsed time ?

Answer 1

will this induce extra over head on the Primary database transaction ? Longer statement elapsed time ?

This depends on how pedantic you'd like to be with the question. Technically speaking, since more operations are needed and other areas can be affected, such as ghost cleanup, then the answer would be yes there is extra overhead.

If you're not being pedantic and just ascertaining if generally enabling a secondary to be readable (but no read workload is executed) would the execution time of queries on the primary be about the same then the answer would be, generically, yes.

So it can be both there is extra overhead and there isn't extra overhead depending on how you classify "extra overhead".

-Sean

Answer 2

Hi @sakuraime ,

Long running transaction could both grow your transaction log and grow your tempdb. It doesn’t even have to be a transaction. It could just be a very long running SELECT statement that will prevent the version store from being cleaned up, as even read-only queries (ie without a transaction) still need to access the version store to ensure consistent reads.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.