2005 Transactional Replication Performance Problems - help please

I have just upgraded from 2000 to 2005 and my transactional replication is running very slow, I already have a latency of 10 mins and its getting worse. I'm just using the default agent profile, is there anything I need to change?

Help please.

Hi Colschmoll,

which build are u use in your 2005 environment?

|||

This was caused by a trigger on one of my replicated tables. It total killed my distibution agent performance, once removed it latency went from 20 mins to 3 seconds in about 10 seconds!!!

Any idea why these triggers worked fin in SQL 2000?

|||

Hello,

I'am on build 9.0.3054.

|||try to find a specifc cumulative hotfix to solve you problem.|||

try to find on this link:

http://support.microsoft.com/default.aspx/kb/936305/

|||

Can you be a little more specific? What triggers?

|||

A bespoke trigger I had on one of the subscriber tables. It was inserting data into another table on the subscriber database. Why this was causing a problem in 2K5, but was okay in 2K is a mystery to me, any ideas?

|||

Did u alread see if is there triggers enabled to replicate from publisher to subscriber?

|||

It's using Stored Procedures to replicate.

|||

Sorry, I would mean that, if your publications has tables with triggers, if you dont want that this trigges going to subscribers you have to configure it to "not for replication".My question was about it.

|||

The trigger which was causing the problem was on the subscriber table?

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
>

2005 Management Studio in slow motion?

I'm running on a dual xeon development box.
Quite often, if I have Visual Studio 2005 up, the Microsoft SQL Server Mangement
Studio will run very slowly.
By slowly, I mean take 2 minutes to open up for modification a stored procedure.
If I run it alone, there is no problem.
What can I do to speed this up?
Thanks in advance, Les Caudle
1) How much ram on the box?
2) Does this occur just the first time the action is attempted or everytime.
The first time incurs a bunch of .NET startup crap.
TheSQLGuru
President
Indicium Resources, Inc.
"Les Caudle" <DotNetWannabe@.newsgroup.nospam> wrote in message
news:bi6a83lj26d12l9l6arcu2kkg6dca8pj8k@.4ax.com...
> I'm running on a dual xeon development box.
> Quite often, if I have Visual Studio 2005 up, the Microsoft SQL Server
> Mangement
> Studio will run very slowly.
> By slowly, I mean take 2 minutes to open up for modification a stored
> procedure.
> If I run it alone, there is no problem.
> What can I do to speed this up?
> --
> Thanks in advance, Les Caudle
|||Hello Les,
I would like to know whether you are using 64 bit machine for Xeon.
I found some related issue which you may refer:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=657867&SiteID=1
One issue that can cause this problem is that if the server does not have
access to the internet, then the .NET framework can't access the
crl.microsoft.com website to verify that the digital signatures used to
sign the binaries for managed applications are valid. Each certificate
check has a 15 second timeout in the .NET runtime implementation.
Depending on what features are installed, this can add up to a minute of
start up time for Management Studio.
There are a couple workarounds:
1) Configure a proxy server to allow access to http://crl.microsoft.com
from your server
2) Configure your firewall to return a failure status quickly when it
blocks access to the http://crl.microsoft.com website
3) Disable checks for certificate revocation. You can do this using
Internet Explorer by opening the Interet Options dialog, going to the
Advanced Page, and then unchecking the "Check for publisher's certificate
revocation" checkbox. There are fraudulently signed binaries in the wild
that can make virus-infected applications look like they were published by
Microsoft. Disabling this check should probably not be done on machines
with internet access.
http://support.microsoft.com/kb/906892/en-us
You may experience slow performance when you run 32-bit SQL Server tools on
64-bit operating systems
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Wei - I'm using 32 bit XP Pro.
CPU usage is very low when this happens.
May be a memory issue, as the box only has 1GB (that used to be a lot).
I guess I'm surprised that it slows down to such a crawl at times, just for
certain operations,, while everything else runs at a normal speed, and only for
SQL Server.
Regards, Les Caudle
On Mon, 02 Jul 2007 02:39:13 GMT, weilu@.online.microsoft.com (Wei Lu [MSFT])
wrote:

>Hello Les,
>I would like to know whether you are using 64 bit machine for Xeon.
>I found some related issue which you may refer:
>http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=657867&SiteID=1
>One issue that can cause this problem is that if the server does not have
>access to the internet, then the .NET framework can't access the
>crl.microsoft.com website to verify that the digital signatures used to
>sign the binaries for managed applications are valid. Each certificate
>check has a 15 second timeout in the .NET runtime implementation.
>Depending on what features are installed, this can add up to a minute of
>start up time for Management Studio.
>There are a couple workarounds:
>1) Configure a proxy server to allow access to http://crl.microsoft.com
>from your server
>2) Configure your firewall to return a failure status quickly when it
>blocks access to the http://crl.microsoft.com website
>3) Disable checks for certificate revocation. You can do this using
>Internet Explorer by opening the Interet Options dialog, going to the
>Advanced Page, and then unchecking the "Check for publisher's certificate
>revocation" checkbox. There are fraudulently signed binaries in the wild
>that can make virus-infected applications look like they were published by
>Microsoft. Disabling this check should probably not be done on machines
>with internet access.
>
>http://support.microsoft.com/kb/906892/en-us
>You may experience slow performance when you run 32-bit SQL Server tools on
>64-bit operating systems
>Hope this helps.
>Sincerely,
>Wei Lu
>Microsoft Online Community Support
>================================================= =
>When responding to posts, please "Reply to Group" via your newsreader so
>that others may learn and benefit from your issue.
>================================================= =
>This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Les,
Thanks for the update.
I would like to suggest you add the physical memory to have a try and let
me know the memory usage.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.