March 14, 2004 at 11:55 am
I have set up replication between two database using both Transactional and Snapshot Replication.
The snapshot replication runs nightly and replicates the majority of the tables. The other tables are replicated via a 15 min transactional replication.
This has worked fine for a while but all of a sudden, a query comprising three tables in the transactional part has started taking several hours to run. It runs in 26 seconds on the source server.
If i take a backup of the source database and restore it to the target server then the query runs in 9 seconds. As soon as i re-create the subscription and the snapshot is applied, the query reverts to taking several hours.
Any help would be greatly appreciated.
March 14, 2004 at 1:41 pm
Two places where I'd look to start with - one is the query being blocked? The other is are you setting up indexes?
Once you've done the initial snapshot is there a reason you're doing later ones? Replication should keep the tables in sync from that point.
March 14, 2004 at 11:55 pm
The query is not being blocked. At the time i am trying it, i am the only user on the DB.
The problem occurs after the initial snapshot is applied on transactional replication. No more snapshots are used or generated.
March 15, 2004 at 4:45 am
So you're saying it is blocking on the publisher, not the subscriber? I know you said there was no blocking - how are you checking? Are you using a concurrent snapshot?
March 15, 2004 at 6:25 am
The replication is running fine, no issues are shown at all. Before the subscription to the transactional replication is created, the query runs quickly. After the subscription has been created and initialised, the query takes several hours to run.
March 15, 2004 at 4:55 pm
How long does the snapshot take?
Might be worth getting someone else to take a look at your system or a call to PSS. Off hand I can think of nothing besides a snapshot that would cause a query to run slower. All the overhead in transactional is in the logreader and posting to the distribution db - basically things that only apply to changes to the data, not selects.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply