February 26, 2007 at 10:10 am
we are having replication problems on sql 2005 but it's only on one table. all other tables are OK. In this case it seems there is a huge amount of reads for every command executed.
we replicated it in our test environment with sql 2005 sp2 and i'm going to try another test in a sql 2000 environment to see if i can replicate it
not sure if it's pure replication, but it seems there is a huge slowdown on the subscribers when the commands are executed.
February 26, 2007 at 7:11 pm
It might seem a little obvious, but check the indexing you have on the table against the joins for your filters.
We experienced similar symptoms to this in SQL 2000 a few years back and tracked it down to this sort of problem. On a table that had very few rows, the queries were going into a tail spin. Once we sorted these out it went fine.
BTW, there are, I think, a few other bugs in SQL Server 2005 replication - particularly when SQL Mobile is involved (mysterious conflicts, subscriptions just dying).
February 26, 2007 at 7:59 pm
i think i tracked the problem down. for some reason it's doing index scans when sending the commands to the subscriber. when i run the same update on the publisher and subscriber via management studio it's index seeks and clustered index update.
the total IO cost in management studio comes out less than 1. via replication it's around 4. only happens on this table and at 1.4 million rows it is not our largest table.
April 3, 2007 at 10:11 am
Had the same problem. Resolved by dropping and recreating clustered index.
Carlos
April 9, 2007 at 1:24 pm
thx
don't know why we didn't think of it. might try it in the next few weeks as we are looking at scheduling an upgrade to sql 2005 for the publisher soon
May 1, 2007 at 10:21 am
Maybe you have strange fragmentation or out of date statistics on your clustered index?
Hanslindgren
May 1, 2007 at 11:41 am
i haven't had time to do a trace, but we went back to sp's for replication and it went away even though we had problems with sp's before. we ran maintenance on the subscribers, but it's a possibility.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply