Rebooting Server for Replication to work

At times, we have come across issues around replication such as high CPU for
transactional replication in SQL 2000 that requires a reboot to fix it.
Do we know why a reboot fixes ? Right now, we just reboot the server, but
dont know how it resolves and what it resolves under the hood ?
We are on SQL SP4. It doesnt happen all the time, just maybe once in 6
months or so.
Thanks
I suspect you are bumping into this.
http://support.microsoft.com/default.aspx/kb/812263
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
"Hassan" <Hassan@.hotmail.com> wrote in message
news:ukenRgwFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> At times, we have come across issues around replication such as high CPU
> for transactional replication in SQL 2000 that requires a reboot to fix
> it.
> Do we know why a reboot fixes ? Right now, we just reboot the server, but
> dont know how it resolves and what it resolves under the hood ?
> We are on SQL SP4. It doesnt happen all the time, just maybe once in 6
> months or so.
> Thanks
>
|||Well maybe I wasnt too clear.. It does work, but it just causes a high CPU
like 90% and things start to slow down on the server. Stopping the SQL Agent
helps but the minute you start it again, it starts to peg again due to the
replication agents.
Any other thoughts ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iEjI2wFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>I suspect you are bumping into this.
> http://support.microsoft.com/default.aspx/kb/812263
> --
> 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
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:ukenRgwFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||Hassan,
as soon as this occurs, you could do some investigation and post back. Use
sp_who2 and DBCC INPUTBUFFER to see what the agent is doing and if there is
blocking involved. Also, use sp_browsereplcommands and MSDistribution_status
to see how what commands are in the queue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||One more thing Hilary, do you think this bug is fixed in SP4 ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iEjI2wFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>I suspect you are bumping into this.
> http://support.microsoft.com/default.aspx/kb/812263
> --
> 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
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:ukenRgwFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||Probably a good idea to move distributor to dedicated box... just 1
occurence in 12 mo. period is justification for this...
Chris MCDBA MCSE
MSSQLConsulting.com
"Hassan" wrote:

> At times, we have come across issues around replication such as high CPU for
> transactional replication in SQL 2000 that requires a reboot to fix it.
> Do we know why a reboot fixes ? Right now, we just reboot the server, but
> dont know how it resolves and what it resolves under the hood ?
> We are on SQL SP4. It doesnt happen all the time, just maybe once in 6
> months or so.
> Thanks
>
>
|||It is unclear if it is or not. It seems that I am bumping into it all the
time on my laptop. I kill the log reader agents and this seems to solve it.
Can you tell if it is the agent which consumes all cpu or SQL Server?
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
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eV6jC9zFHHA.4588@.TK2MSFTNGP05.phx.gbl...
> One more thing Hilary, do you think this bug is fixed in SP4 ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23iEjI2wFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||I'll check the next time it occurs..
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23VqLoW7FHHA.1276@.TK2MSFTNGP04.phx.gbl...
> It is unclear if it is or not. It seems that I am bumping into it all the
> time on my laptop. I kill the log reader agents and this seems to solve
> it.
> Can you tell if it is the agent which consumes all cpu or SQL Server?
> --
> 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
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:eV6jC9zFHHA.4588@.TK2MSFTNGP05.phx.gbl...
>

Really really slow cursor

I'm stumped on this.
My developers proc left to run all night consumes tons of cpu but does no
updates at all and I have to kill the connection in the morning.
The select in the cursor declaration returns 357 rows so not an outrageous
result set. When I run the select in Qry Analyser it runs in under 6 seconds
.
When I run the stored proc in QA it takes forever. Debug print statements
appear up until the initial fetch and then nothing.
Can a cursor loop indefinitely?
CREATE PROCEDURE dbo.stc_Insert_Instrument AS
set nocount on
declare
@.rc int -- returncode
, @.errmsg varchar(250)
, @.msg varchar(50)
select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
-- create temp tables
select
<Snip>
into #moodystaging
from moodystaging
select
<Snip>
into #spstaging
from spstaging
declare ins_cursor cursor read_only for
select
iss.IssuerId,
st.SECURITY_DES,
case isdate(st.maturity)
when 1 then st.maturity
else null
end as maturity,
case isdate(st.issue_dt)
when 1 then st.issue_dt
else null
end as issue_dt,
case isnumeric(st.cpn)
when 1 then st.cpn
else null
end as cpn,
cp.CouponTypeId,
st.CRNCY,
case isnumeric(st.AMT_ISSUED)
when 1 then st.AMT_ISSUED
else null
end as amt_issued,
case isnumeric(st.AMT_OUTSTANDING)
when 1 then st.AMT_OUTSTANDING
else null
end as amt_outstanding,
r.RatingId,
sprt.RatingId as spRatingId,
st.id_bb_unique,
st.id_isin,
st.id_cusip
from
staging st
left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
left join external e on st.id_bb_unique = e.externalid and
e.externaltype='BB'
left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
left join rating r on mt.rtg = r.rating and r.type = 'Moody'
left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
where
e.externalid is null
and iss.sourceid is not null
open ins_cursor
declare
@.issuerid int,
@.instrument_name varchar(50),
@.maturitydate datetime,
@.issuedate datetime,
@.coupon float(8),
@.coupontypeid int,
@.currency varchar(3),
@.amountissued float(8),
@.amountoutstanding float(8),
@.moodyratingid int,
@.spratingid int,
@.bb_id varchar(50),
@.isin varchar(50),
@.cusip varchar(50),
@.instrumentid int
fetch next from ins_cursor into
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid,
@.bb_id,
@.isin,
@.cusip
print 'after first fetch'
while @.@.fetch_status = 0
begin
print 'processing'
insert into instrument (
issuerid,
instrumentname,
maturitydate,
issuedate,
coupon,
coupontypeid,
currency,
amountissued,
amountoutstanding,
moodyratingid,
spratingid)
values (
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into Instrument failed.'
goto errhandler
end
set @.instrumentid = @.@.identity
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'BB', @.bb_id)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for BB type.'
goto errhandler
end
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'ISIN', @.isin)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for ISIN type.'
goto errhandler
end
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'Cusip', @.cusip)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for Cusip type.'
goto errhandler
end
fetch next from ins_cursor into
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid,
@.bb_id,
@.isin,
@.cusip
end
close ins_cursor
deallocate ins_cursor
drop table #moodystaging
drop table #spstaging
return 0 -- success
errhandler:
raiserror ('%s %s',16,1,@.errmsg,@.msg)
if @.@.trancount > 0
rollback transaction
drop table #moodystaging
drop table #spstaging
return @.rc
GOMaybe one of the tables that you are updating is locked and the stored
procedure is waiting for the lock to free up?
"Si" <Si@.discussions.microsoft.com> wrote in message
news:4A962893-F4D1-4BDA-814E-9DB0E891EA6F@.microsoft.com...
> I'm stumped on this.
> My developers proc left to run all night consumes tons of cpu but does no
> updates at all and I have to kill the connection in the morning.
> The select in the cursor declaration returns 357 rows so not an outrageous
> result set. When I run the select in Qry Analyser it runs in under 6
seconds.
> When I run the stored proc in QA it takes forever. Debug print statements
> appear up until the initial fetch and then nothing.
> Can a cursor loop indefinitely?
>
>
> CREATE PROCEDURE dbo.stc_Insert_Instrument AS
> set nocount on
> declare
> @.rc int -- returncode
> , @.errmsg varchar(250)
> , @.msg varchar(50)
>
> select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
> -- create temp tables
> select
> <Snip>
> into #moodystaging
> from moodystaging
>
> select
> <Snip>
> into #spstaging
> from spstaging
>
> declare ins_cursor cursor read_only for
> select
> iss.IssuerId,
> st.SECURITY_DES,
> case isdate(st.maturity)
> when 1 then st.maturity
> else null
> end as maturity,
> case isdate(st.issue_dt)
> when 1 then st.issue_dt
> else null
> end as issue_dt,
> case isnumeric(st.cpn)
> when 1 then st.cpn
> else null
> end as cpn,
> cp.CouponTypeId,
> st.CRNCY,
> case isnumeric(st.AMT_ISSUED)
> when 1 then st.AMT_ISSUED
> else null
> end as amt_issued,
> case isnumeric(st.AMT_OUTSTANDING)
> when 1 then st.AMT_OUTSTANDING
> else null
> end as amt_outstanding,
> r.RatingId,
> sprt.RatingId as spRatingId,
> st.id_bb_unique,
> st.id_isin,
> st.id_cusip
> from
> staging st
> left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
> INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
> left join external e on st.id_bb_unique = e.externalid and
> e.externaltype='BB'
> left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
> left join rating r on mt.rtg = r.rating and r.type = 'Moody'
> left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
> left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
> where
> e.externalid is null
> and iss.sourceid is not null
>
> open ins_cursor
> declare
> @.issuerid int,
> @.instrument_name varchar(50),
> @.maturitydate datetime,
> @.issuedate datetime,
> @.coupon float(8),
> @.coupontypeid int,
> @.currency varchar(3),
> @.amountissued float(8),
> @.amountoutstanding float(8),
> @.moodyratingid int,
> @.spratingid int,
> @.bb_id varchar(50),
> @.isin varchar(50),
> @.cusip varchar(50),
> @.instrumentid int
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> print 'after first fetch'
> while @.@.fetch_status = 0
> begin
> print 'processing'
> insert into instrument (
> issuerid,
> instrumentname,
> maturitydate,
> issuedate,
> coupon,
> coupontypeid,
> currency,
> amountissued,
> amountoutstanding,
> moodyratingid,
> spratingid)
> values (
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into Instrument failed.'
> goto errhandler
> end
> set @.instrumentid = @.@.identity
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'BB', @.bb_id)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for BB type.'
> goto errhandler
> end
>
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'ISIN', @.isin)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for ISIN type.'
> goto errhandler
> end
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'Cusip', @.cusip)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for Cusip type.'
> goto errhandler
> end
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> end
> close ins_cursor
> deallocate ins_cursor
> drop table #moodystaging
> drop table #spstaging
> return 0 -- success
> errhandler:
> raiserror ('%s %s',16,1,@.errmsg,@.msg)
> if @.@.trancount > 0
> rollback transaction
> drop table #moodystaging
> drop table #spstaging
> return @.rc
> GO
>|||One thing is clear - this can be done without a cursor. Or have I missed
something?
What else is going on in there during the night?
ML
http://milambda.blogspot.com/|||The reason for the cursor was to extract the identity value from the
instrument table to enter it into the external table. (Sorry, I didn't
include table definitions)
I'd be very happy if there isanother way to achieve this and get rid of the
curse, I mean cursor!
There is nothing else going on overnight apart from backups.
"ML" wrote:

> One thing is clear - this can be done without a cursor. Or have I missed
> something?
> What else is going on in there during the night?
>
> ML
> --
> http://milambda.blogspot.com/|||Thanks Jim,
I don't think this is the case but I'll double check.
Progress seems to halt before then, right after the initial fetch.
Simon
"Jim Underwood" wrote:

> Maybe one of the tables that you are updating is locked and the stored
> procedure is waiting for the lock to free up?
> "Si" <Si@.discussions.microsoft.com> wrote in message
> news:4A962893-F4D1-4BDA-814E-9DB0E891EA6F@.microsoft.com...
> seconds.
>
>|||So the 'after first fetch'
is never reached?
"Si" <Si@.discussions.microsoft.com> wrote in message
news:E15851B6-2879-453D-8F01-C96375BF7A1C@.microsoft.com...
> Thanks Jim,
> I don't think this is the case but I'll double check.
> Progress seems to halt before then, right after the initial fetch.
> Simon
>
> "Jim Underwood" wrote:
>
no
outrageous
statements|||Si wrote:

> The reason for the cursor was to extract the identity value from the
> instrument table to enter it into the external table. (Sorry, I didn't
> include table definitions)
> I'd be very happy if there isanother way to achieve this and get rid of th
e
> curse, I mean cursor!
>
At least three possible solutions that don't need a cursor.
1. Create an insert trigger on the Instrument table to populate
External.
2. Use a table variable or temp table (SQL Server 2000):
DECLARE @.instrument TABLE ...;
INSERT INTO @.instrument (...)
SELECT ...
FROM staging ...;
INSERT INTO instrument (...)
SELECT ...
FROM @.instrument;
INSERT INTO external
(instrumentid, ...)
SELECT I.instrumentid, ...
FROM instrument AS T
LEFT JOIN @.instrument AS I
ON ... etc
3. Use a table variable and INSERT ... OUTPUT (SQL Server 2005):
DECLARE @.instrument (instrumentid INTEGER);
INSERT INTO instrument (...)
OUTPUT Inserted.instrumentid INTO @.instrument
SELECT ...
FROM staging ...;
INSERT INTO external
(instrumentid, ...)
SELECT instrumentid, ...
FROM @.instrument;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||curse (n) - an unnecessary cursor
I like that. :) Maybe it should be added:
http://www.webster.com/dictionary/curse
ML
http://milambda.blogspot.com/|||You don't need a cursor for a select.. insert..
insert into TableA( a, b, c )
select a, b, c from TableB
Meanwhile, the next time it seems to freeze, use the procedure sp_who2 to
determine if the process is blocked.
"Si" <Si@.discussions.microsoft.com> wrote in message
news:717A26A2-8862-49D0-A78D-44879CD2FA3B@.microsoft.com...
> The reason for the cursor was to extract the identity value from the
> instrument table to enter it into the external table. (Sorry, I didn't
> include table definitions)
> I'd be very happy if there isanother way to achieve this and get rid of
> the
> curse, I mean cursor!
>
> There is nothing else going on overnight apart from backups.
> "ML" wrote:
>|||Some re-thinking on the design and cursor no longer needed.
Thanks very much everyone for your time and replies.
Simon.
"Si" wrote:

> I'm stumped on this.
> My developers proc left to run all night consumes tons of cpu but does no
> updates at all and I have to kill the connection in the morning.
> The select in the cursor declaration returns 357 rows so not an outrageous
> result set. When I run the select in Qry Analyser it runs in under 6 secon
ds.
> When I run the stored proc in QA it takes forever. Debug print statements
> appear up until the initial fetch and then nothing.
> Can a cursor loop indefinitely?
>
>
> CREATE PROCEDURE dbo.stc_Insert_Instrument AS
> set nocount on
> declare
> @.rc int -- returncode
> , @.errmsg varchar(250)
> , @.msg varchar(50)
>
> select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
> -- create temp tables
> select
> <Snip>
> into #moodystaging
> from moodystaging
>
> select
> <Snip>
> into #spstaging
> from spstaging
>
> declare ins_cursor cursor read_only for
> select
> iss.IssuerId,
> st.SECURITY_DES,
> case isdate(st.maturity)
> when 1 then st.maturity
> else null
> end as maturity,
> case isdate(st.issue_dt)
> when 1 then st.issue_dt
> else null
> end as issue_dt,
> case isnumeric(st.cpn)
> when 1 then st.cpn
> else null
> end as cpn,
> cp.CouponTypeId,
> st.CRNCY,
> case isnumeric(st.AMT_ISSUED)
> when 1 then st.AMT_ISSUED
> else null
> end as amt_issued,
> case isnumeric(st.AMT_OUTSTANDING)
> when 1 then st.AMT_OUTSTANDING
> else null
> end as amt_outstanding,
> r.RatingId,
> sprt.RatingId as spRatingId,
> st.id_bb_unique,
> st.id_isin,
> st.id_cusip
> from
> staging st
> left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
> INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
> left join external e on st.id_bb_unique = e.externalid and
> e.externaltype='BB'
> left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
> left join rating r on mt.rtg = r.rating and r.type = 'Moody'
> left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
> left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
> where
> e.externalid is null
> and iss.sourceid is not null
>
> open ins_cursor
> declare
> @.issuerid int,
> @.instrument_name varchar(50),
> @.maturitydate datetime,
> @.issuedate datetime,
> @.coupon float(8),
> @.coupontypeid int,
> @.currency varchar(3),
> @.amountissued float(8),
> @.amountoutstanding float(8),
> @.moodyratingid int,
> @.spratingid int,
> @.bb_id varchar(50),
> @.isin varchar(50),
> @.cusip varchar(50),
> @.instrumentid int
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> print 'after first fetch'
> while @.@.fetch_status = 0
> begin
> print 'processing'
> insert into instrument (
> issuerid,
> instrumentname,
> maturitydate,
> issuedate,
> coupon,
> coupontypeid,
> currency,
> amountissued,
> amountoutstanding,
> moodyratingid,
> spratingid)
> values (
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into Instrument failed.'
> goto errhandler
> end
> set @.instrumentid = @.@.identity
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'BB', @.bb_id)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for BB type.'
> goto errhandler
> end
>
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'ISIN', @.isin)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for ISIN type.'
> goto errhandler
> end
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'Cusip', @.cusip)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for Cusip type.'
> goto errhandler
> end
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> end
> close ins_cursor
> deallocate ins_cursor
> drop table #moodystaging
> drop table #spstaging
> return 0 -- success
> errhandler:
> raiserror ('%s %s',16,1,@.errmsg,@.msg)
> if @.@.trancount > 0
> rollback transaction
> drop table #moodystaging
> drop table #spstaging
> return @.rc
> GO
>

Real Impact of MS Performance Counters

; have been collecting information about 20 performance counters (memory, IO,
cpu, SQL) that refresh every 15 seconds; would that have any performance hit
in the server? what are best practices when collecting information via
performance counters?
ThanksCollecting these counter values at a 15-second interval should have
absolutely no performance impact. (If on the other hand for however small
chance it does have an impact, your machine is greatly under-spec'ed). No, I
wouldn't worry about any performance impact at all.
Linchi
"Carlos" wrote:
> ; have been collecting information about 20 performance counters (memory, IO,
> cpu, SQL) that refresh every 15 seconds; would that have any performance hit
> in the server? what are best practices when collecting information via
> performance counters?
> Thanks