All we need is an easy explanation of the problem, so here it is.
Enabling readable secondary will result in addition of 14byte extra information to each row in the primary as well as secondary.
This will also automatically enable the snapshot isolation level on the secondary which will result in additional load on the secondary server’s tempdb.
Is there any chance this will put any load on the primary server’s tempdb?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
The load on TempDB in an availability group will be determined by the work load happening on a given replica, plus the isolation level of the replica. With a secondary replica having read enabled, it’s now possible for workloads to occur on that replica, and impact TempDB. Additionally, RCSI is automatically enabled on readable secondaries, resulting in extra overhead on TempDB as different versions of rows are stored in TempDB as queries are running.
However, the primary will not take on this same isolation behavior, simply due to the secondary having reads enabled. Without RCSI being enabled on the primary, it will not see the additional overhead.
Now, if you choose to enable RCSI on the primary, then it’s TempDB usage would be impacted in the same manner as the readable secondary replicas.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂