Hi,
In SQL 2005 Replication Monitor i was not seeing details for any of the publications on the "Distributor to Subscriber Histroy Tab" so i decided to stop and start synchronisation on this one publication. At this time there were approximayely 20000 undistributed commands. After the stop/start of the distribution agent, i started seeing messages like "x trasactions with x commands were delivered". Then i went and restarted all the other distribution agents using the Replication Monitor.
Has anyone experienced this kind of a behaviour?
The second issue is that our trasnactional replication looked to have caught up but i was supprised find that the distribution server was running at 100%. A profiler trace of the distribution database revealed that sp_MSget_repl_commands procedure was being executed and costing approximately in excess of 400 000 reads, 7000 in CPU cost and 15sec in duration. To me it looked as if sp_MSget_repl_commands has chosen an inefficient execution plan but then realised i couldn't recompile system procedures. I think a stop and start of the SQL instance is the only option i have.
PK
You can't recompile the proc itself, but you can force a recompile by updating the stats on the table. This should in theory trigger a recompile for the proc, and I'm sure it applies to procs in the resource db as well, but don't quote me on it.
Do you know if it was the replication monitor or the distribution agent that was making the proc call you were profiling? In pre-RTM days, there was a performance bug for the call made by the replication monitor, making it very expensive. I assume you're on RTM version of SQL 2005?
Without knowing what the plan looks like for the queries inside the proc, it's hard to say what the problem is.
No comments:
Post a Comment