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.com/performance_monitor_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:
> 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
> >|||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.
>Thanks
>--Vishal
>
>Hilary Cotter wrote:
>> 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
>> >