Can heavy read load on subscriber/replica db (used for analytics purpose) can hit the performance of sql server transaction replication from publisher to subscriber/replica?
We have noticed that when transactional replication lags, and we stop heavy read on subscriber, lags vanish after some time.
Is this the expected behaviour?
Can heavy read load on subscriber/replica db (used for analytics purpose) can hit the performance of sql server transaction replication from publisher to subscriber/replica?
Depends where in the stack you mean. It'll never affect the performance of the Publisher's server or database. But if it's causing resource contention on the Subscriber server then of course most things will run slower on the Subscriber server, regardless.
We have noticed that when transactional replication lags, and we stop heavy read on subscriber, lags vanish after some time.
Sounds like resource contention, which could slow down the synchronization of the data to the Subscriber, among slowing down other things trying to run on that server. You probably should look into what resources those queries are consuming and look for ways to optimize them. You might find sp_WhoIsActive helpful as a start to tracing them.
