insert performance varies

hi. i'm no dba and wouldn't mind a few pointers for investigation of an issu
e
i have with sql server 2000 sp3. i am running a script within query analyzer
.
the batch includes hundreds of deletes followed by hundreds of inserts, all
within a single transaction.
i have noticed that running the batch multiple times results in widely
varying run times (between 9 and 100 seconds). however, the time taken to do
the deletes never varies (always around 2.5 seconds). how might i begin
investigating this? assuming no other queries are being run against the
database, is there anything sql server might be doing in the background whic
h
might cause this.
note one of my colleagues found this link, which sounds similar to our
problem. unfortunately applying sp4 is out of the question at the moment.
http://support.microsoft.com/kb/835864
many thanks
khA wild guess is that autogrow for the log file (or even database file) kicks
in when it is slow.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kh" <kh@.nospam.nospam> wrote in message news:50CF0E7F-3653-4600-998E-90323DF73F5C@.microsoft
.com...
> hi. i'm no dba and wouldn't mind a few pointers for investigation of an is
sue
> i have with sql server 2000 sp3. i am running a script within query analyz
er.
> the batch includes hundreds of deletes followed by hundreds of inserts, al
l
> within a single transaction.
> i have noticed that running the batch multiple times results in widely
> varying run times (between 9 and 100 seconds). however, the time taken to
do
> the deletes never varies (always around 2.5 seconds). how might i begin
> investigating this? assuming no other queries are being run against the
> database, is there anything sql server might be doing in the background wh
ich
> might cause this.
> note one of my colleagues found this link, which sounds similar to our
> problem. unfortunately applying sp4 is out of the question at the moment.
> http://support.microsoft.com/kb/835864
> many thanks
> kh
>|||Hi,
I understand the the the delay only occurs for insert operations. As you
said, 835864 shall be a possible cause if you are use multi-processes or
hyperthread CPUs. If it is the cause, I'm afraid that install latest SP4
when possible shall be the only fix.
You may want to use the following statements and check the difference of
each execution.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
dbcc freeproccache
dbcc dropcleanbuffers
Go
--Exec <place your stored procedure or query here>
go
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
go
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
It's a little werid that only insert time varies. You may want to check if
there is any trigger on the table for insert. Autogrow of the database/log
as Tibor mentioned might be a problem.
Also, I'd like to know if you have clustred index on the table? Are the
rows you insert/delete are the exactly same? Since statistics information
might be different if the rows are not same and this may affect execution
plan.
If you have any update, please feel free to let's know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I'm still interested in the issue. Did you try the suggestions to see the
difference of execution? If you have any update, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============