hello, we have a transactional replication with 1 article for 1 table with 2 subscribers (publisher and distributor are on the same server). This table is sized around 100G and index on it is sized around 200G. at times transferring data to subscriber runs very slow. When i use tracer tokens i see there is no delay in publisher to distributor , however distr. to subscriber has a very long latency sometimes around 1 hour or more.
How can i troubleshoot to see what is happening on subscriber side. Is there any way to find if large update or delete or insert operation happened on the publisher side which cause this delay?
Can issue caused by busy publisher in delay in transferring data to subscriber ?
Few points.
There are no blocks on the subscribers but most of the time i see pageiolatch_ex or _sh waits on the subscriber for the replication connections sessions. Table has around 27 non-clustered indexes, thats a worrysome issue i know, but i need find out what is happening when delays like this are going on.