High CPU utilization

Could anyone help me in finding out why the cpu utilization is very
high'
I have two servers say, Server A , server B. There is a transactional
replication going on from server A to B
There is a table say Table A on server A, which is being replicated to
server B.
I created a trigger insert and update trigger on Table A on server B
(i.e. on subscriber). Since then, the CPU utilization for server B is
very high 80-90%
when i used profiler, i could see .whenever replication stored proc for
insert or update executes..cpu utilization goes up..
trigger just insert the updated/inserted rows into some other table.
Could anyone tell me why the cpu utilization has gone up so much' Does
Creating trigger on the subscriber cause performance problem' i am
using sql server 2005
thanxVishal
> trigger just insert the updated/inserted rows into some other table.
> Could anyone tell me why the cpu utilization has gone up so much' Does
> Creating trigger on the subscriber cause performance problem' i am
> using sql server 2005
Yes it is
http://www.sql-server-performance.c...or_counters.asp
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161069980.591285.61830@.b28g2000cwb.googlegroups.com...
> Could anyone help me in finding out why the cpu utilization is very
> high'
> I have two servers say, Server A , server B. There is a transactional
> replication going on from server A to B
> There is a table say Table A on server A, which is being replicated to
> server B.
> I created a trigger insert and update trigger on Table A on server B
> (i.e. on subscriber). Since then, the CPU utilization for server B is
> very high 80-90%
> when i used profiler, i could see .whenever replication stored proc for
> insert or update executes..cpu utilization goes up..
> trigger just insert the updated/inserted rows into some other table.
> Could anyone tell me why the cpu utilization has gone up so much' Does
> Creating trigger on the subscriber cause performance problem' i am
> using sql server 2005
> thanx
>|||What is this trigger doing? Frequently when you want a trigger fired as part
of a replication process you should place the triggering action in a
different table. So if you want the trigger fired when you have DML against
table a, and the trigger writes to table b, you get better performance by
creating a separate article which writes to table b, and encapsulate the
trigger logic in the stored procedure.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161069980.591285.61830@.b28g2000cwb.googlegroups.com...
> Could anyone help me in finding out why the cpu utilization is very
> high'
> I have two servers say, Server A , server B. There is a transactional
> replication going on from server A to B
> There is a table say Table A on server A, which is being replicated to
> server B.
> I created a trigger insert and update trigger on Table A on server B
> (i.e. on subscriber). Since then, the CPU utilization for server B is
> very high 80-90%
> when i used profiler, i could see .whenever replication stored proc for
> insert or update executes..cpu utilization goes up..
> trigger just insert the updated/inserted rows into some other table.
> Could anyone tell me why the cpu utilization has gone up so much' Does
> Creating trigger on the subscriber cause performance problem' i am
> using sql server 2005
> thanx
>|||Thank you for your reply.
Let me explain you lil further. What i have is a server A and Server B.
On server A I have a database say dbA which is being replicated to
server B using transactional replication. In dbA, i have a table say
table A which is being replicated to server B (ReplTableA) .
Now I am interested in tracking the changes occring to the records of
table A. So whenever there is an insert in table A, I want to insert
that record in history table. Whenever there is an update in table A, I
want to insert that updated record in the history table. So for this i
am using a trigger. but instead of creating a trigger on table A on dbA
(on server A), I am creating a trigger on the replicated
table(ReplTableA) on server B (I thoght this will improve the
performance on server A) . So whenever replication procedure (for
insert & update for ReplTableA) executes, the trigger fires.
Beacause of this cpu utilization of server B has gone up so much (80-90
%)
So is it good thing to write a trigger on the ReplTableA or should i
write a trigger on the original table Table A on server A?
Or is there any thing i can do to reduce the CPU utilization? Could you
please elaborate on creating a seperate artical and encapsulating the
trigger logic in a stored proc ?
Thanks
--Vishal
Hilary Cotter wrote:[vbcol=seagreen]
> What is this trigger doing? Frequently when you want a trigger fired as pa
rt
> of a replication process you should place the triggering action in a
> different table. So if you want the trigger fired when you have DML agains
t
> table a, and the trigger writes to table b, you get better performance by
> creating a separate article which writes to table b, and encapsulate the
> trigger logic in the stored procedure.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161069980.591285.61830@.b28g2000cwb.googlegroups.com...|||On 17 Oct 2006 10:12:13 -0700, "Vishal" <rvishal1305@.gmail.com> wrote:
>So is it good thing to write a trigger on the ReplTableA or should i
>write a trigger on the original table Table A on server A?
Should be OK on serverB.

>Or is there any thing i can do to reduce the CPU utilization? Could you
>please elaborate on creating a seperate artical and encapsulating the
>trigger logic in a stored proc ?
Aha, he seems to be suggesting using a redundant replication article
instead of a trigger. Elegant idea!
But really, the trigger should work, too.
Can you post the SQL for the trigger and the DDL for the table and
indexes? Odds are it's just a missing index or something along those
lines.
J.
[vbcol=seagreen]
>Thanks
>--Vishal
>
>Hilary Cotter wrote: