2005 Merge Agent Degradation

I have a merge agent running extremely slow - schedule fires every 1 minute,
however it takes 1 to 3 minutes to execute. Delivery Rate 5 rows/sec or less,
all 5 other merge agents execute in less than 20 seconds and Delivery Rate 80
rows/sec or greater.
Each time merge agent fires, subscr cpu spikes to 85% - normally 15 to 20%.
Ran profiler and discovered this stamement below hogging the cpu and having
the longest duration.
I am rebuilding the MSmerge_genhistory table nightly w/ the following: DBCC
DBREINDEX (MSmerge_genhistory, '', 80)
Any ideas? A re-init will resolve problem , have had to do this many times
in past - at least every 30 days... however, want to get to root cause. tia
Chris
select top (@.numgens) *
from
(
select generation, guidsrc, art_nick,
case when genstatus = 4 then 0 else genstatus end as
genstatus,
pubid, nicknames,
okaytoskip = case when
art_nick is not null and art_nick <> 0
and genstatus in (0,4)
-- Skip all rows that are for incomplete
generations for articles that have no joins.
and not exists (select 1 from
dbo.sysmergesubsetfilters where (join_nickname = art_nick or art_nickname =
art_nick) and (filter_type & 1) = 1)
then 1 else 0 end
, changecount
from
(select generation, guidsrc, art_nick, genstatus, pubid,
nicknames, changecount
from dbo.MSmerge_genhistory with (rowlock, repeatableread)
where generation >= @.genstart
and generation <= @.maxgen_to_enumerate
and generation > @.mingen_to_enumerate
and (art_nick = 0 or art_nick is NULL or
art_nick in (select nickname from dbo.sysmergearticles
where pubid = @.pubid))
) as generation_range
UNION ALL -- use UNION ALL instead of UNION for perf
reasons. Merge agent code will skip dupes. Will only have max 2 dupes.
select generation = @.next_possible_watermark, guidsrc =
@.next_possible_watermark_guidsrc,
art_nick = @.next_possible_watermark_art_nick, genstatus =
@.next_possible_watermark_genstatus,
pubid = @.next_possible_watermark_pubid, nicknames =
@.next_possible_watermark_nicknames,
okaytoskip = 0, changecount =
@.next_possible_watermark_changecount
where @.next_possible_watermark is not null
union all
select generation = @.min_open_gen, guidsrc= @.min_open_gen_guid,
art_nick=@.min_open_gen_art_nick, genstatus=0,
pubid=@.pubid, nicknames=NULL, okaytoskip = 0,
changecount=0
where @.min_open_gen is not null
) as genertions
order by generation ASC
Two things I can suggest here is:
1. Avoid metadata contention. Make sure merge agents do not run at the
same time so stagger their schedules.
2. Retention period can cause lots of data to be stored in the contets
and tombstone tables. Default i think is 14 days. So 14 days worth of
changes are stored. Consider reducing this if subscribers are not
offline for a long time.
Regards Jim
http://jims-spanakopita.blogspot.com/
|||What do your join filters look like? By placing indexes on all the filters
you will get better performance. Another option to try is to modify your
filters to make them as shallow as possible.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4ED9E8B8-9CA1-4B10-A609-E67921202C68@.microsoft.com...
> I have a merge agent running extremely slow - schedule fires every 1
> minute,
> however it takes 1 to 3 minutes to execute. Delivery Rate 5 rows/sec or
> less,
> all 5 other merge agents execute in less than 20 seconds and Delivery Rate
> 80
> rows/sec or greater.
> Each time merge agent fires, subscr cpu spikes to 85% - normally 15 to
> 20%.
> Ran profiler and discovered this stamement below hogging the cpu and
> having
> the longest duration.
> I am rebuilding the MSmerge_genhistory table nightly w/ the following:
> DBCC
> DBREINDEX (MSmerge_genhistory, '', 80)
>
> Any ideas? A re-init will resolve problem , have had to do this many times
> in past - at least every 30 days... however, want to get to root cause.
> tia
> Chris
>
> select top (@.numgens) *
> from
> (
> select generation, guidsrc, art_nick,
> case when genstatus = 4 then 0 else genstatus end as
> genstatus,
> pubid, nicknames,
> okaytoskip = case when
> art_nick is not null and art_nick <> 0
> and genstatus in (0,4)
> -- Skip all rows that are for incomplete
> generations for articles that have no joins.
> and not exists (select 1 from
> dbo.sysmergesubsetfilters where (join_nickname = art_nick or art_nickname
> =
> art_nick) and (filter_type & 1) = 1)
> then 1 else 0 end
> , changecount
> from
> (select generation, guidsrc, art_nick, genstatus, pubid,
> nicknames, changecount
> from dbo.MSmerge_genhistory with (rowlock, repeatableread)
> where generation >= @.genstart
> and generation <= @.maxgen_to_enumerate
> and generation > @.mingen_to_enumerate
> and (art_nick = 0 or art_nick is NULL or
> art_nick in (select nickname from dbo.sysmergearticles
> where pubid = @.pubid))
> ) as generation_range
> UNION ALL -- use UNION ALL instead of UNION for perf
> reasons. Merge agent code will skip dupes. Will only have max 2 dupes.
> select generation = @.next_possible_watermark, guidsrc =
> @.next_possible_watermark_guidsrc,
> art_nick = @.next_possible_watermark_art_nick, genstatus =
> @.next_possible_watermark_genstatus,
> pubid = @.next_possible_watermark_pubid, nicknames =
> @.next_possible_watermark_nicknames,
> okaytoskip = 0, changecount =
> @.next_possible_watermark_changecount
> where @.next_possible_watermark is not null
> union all
> select generation = @.min_open_gen, guidsrc= @.min_open_gen_guid,
> art_nick=@.min_open_gen_art_nick, genstatus=0,
> pubid=@.pubid, nicknames=NULL, okaytoskip = 0,
> changecount=0
> where @.min_open_gen is not null
> ) as genertions
> order by generation ASC
>