Distribution task marvelously slow

Greetings!
We are replicating a table (many of them actually, but one in particular)
which is about 10 GB...660K rows including some text columns. We needed to
change the properties of one column and did not want to pay the penalty of
reinitializing the subscription, so I added a temp column "A_temp", populated
that with the data from the original column "A" then dropped column "A" and
readded it with new properties including NOT NULL. We are then loading "A"
from "A_temp" and then dropping the "A_temp" column. In theory this is fine
and it is working...however the updates are remarkably slow...three records a
second with the sp_MSUpd_... stored procedure. At that rate, the whole
shebang will take days instead of hour or so it would have with the reinit
and snapshot. This is generally the way MS would recommend doing the schema
change from what I have studied. The publisher and distributor are same
server and the subscriber is a remote, but on the LAN, machine.
Any ideas? Thanks so much!
You could check the query plan of the update stored proc - perhaps it would
benefit from different indexes at the subscriber or perhaps the indexes are
fragmented - also worth checking. Finally, check to see if there is any
blocking going on that accounts for the slowdown.
HTH,
Paul Ibison
|||Thank you for your reply. I did notice that the replication update was
blocking other things, but we have rectified that - for now. The actual
update doesn't appear to be blocked at all. We do have the column with a
clustered index on it. Actually thought of dropping indexes to see if it
sped up, but the column which is getting updated is not indexes, so I wasn't
expected a lot out of that. I haven't checked out the query plan...I think
you are right that it is worth a look. I'll see if I can get one of my
admins to toss me an actual sp call instead of having me guess at the
parameters...
Thanks again!
"Paul Ibison" wrote:

> You could check the query plan of the update stored proc - perhaps it would
> benefit from different indexes at the subscriber or perhaps the indexes are
> fragmented - also worth checking. Finally, check to see if there is any
> blocking going on that accounts for the slowdown.
> HTH,
> Paul Ibison
>

Distribution Agent - Replication Monitor warning

Hi,

I am replicating a large table with a number of indexes. During the initialisation phase (creating the indexes at the subscriber), I am seing the following error/warning in Replication monitor. When the initialisation is complete the error warnings dissapear.

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Is there a way to increase the 10 minute time-limit?

Thanks,
Priyanga

Hi Priyanga,

You can use the -KeepAliveMessage parameter of the distribution agent to specify a larger time interval for logging the "agent suspect" messages. However, I am a bit hesitant to recommend that you simply increase the time interval in general since you would probably want to be notified that the distribution agent may be stalled in a more timely manner when it is delivering incremental changes. On the other hand, delivery of snapshot is arguably a rare enough occurrence that the "agent suspect" messages should not become a significant source of annoyance.

In truth, the following factors had conspired to make the "agent suspect" messages almost unavoidable when delivering a large snapshot:

1) BCP API does not allow the distribution agent to log a progress message until every "BcpBatchSize" number of rows have been bulk-loaded into a subscriber table
2) We use a large "BcpBatchSize" (2^31 - delta) as the default "BcpBatchSize" in SQL2005 so the distribution agent can more readily meet the minimally-logged bulk-load requirements.
3) The time required to create an index on a large table probably exceeds the default -KeepAliveMessageInterval

Based on 1) and 2) above, distribution agent will mostly not be able to log a progress message in-between a bulk-load operation;and if the amount of data that needs to be bulk-loaded is large, the distribution agent will likely not be able to finish the bulk-load operation within the default -KeepAliveMessageInterval.

HTH

-Raymond