We have processes which load data collected from around the country in
night time DTS processes. Tasks are loaded and run one on the heels of
another. These have a pretty consistent performance profile most of the
time. But sometimes, almost always at night, we see a 10 fold increase
in the wall time needed for some of these loads. This can be enough to
cause timeouts in DTS ActiveX scripts using ADO.

Our theory is that SQL is doing extents or doing some other processing
behind the scenes.

Seeking advice on what kinds of things we can do to get a better handle
on these long run times, and if possible, eliminate them.

Thanks for any wisdom
Best regards
Lee Gillie
Online Data Processing, Inc.
Spokane, WA

In message <u4xI4lzxEHA.3168 (AT) TK2MSFTNGP15 (DOT) phx.gbl>, Lee Gillie
<Lee (AT) nospam (DOT) odp.com> writes
When do you backups run? At night perhaps, which as quote an intensive
IO operation, could conflict with an IO intensive load.

Have you tracked the volume of data being loaded vs the time taken? Is
there more data on the slow runs?

Does a slow run have more data, and perhaps it runs over the normal
time, and then maybe clashes with a backup or similar maintenance
routine. Two such routines at once may take 3xTime, but two in series
may only take 2xTime as "collisions" may occur when running in parallel.

What maintenance plans or similar do you have?

Is there some pattern?

Lots of factors, but for example perhaps you are getting a lot of page
splits one night, then it goes faster the next time as you paid the page
split price previously. Then the pages become full again, so the splits
start again. This might show as a pattern with a fixed amount of data
each night, but could vary if the data amount varies.

I would also monitor network usage, if the load is across machines. It
maybe someone else is running a big job that causes reduced bandwidth
for you.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org