I have a system at a client site that runs an executable from SQL Server
Agent, that runs a bunch of stored procedures and writes the output of each
one to files (about 1600 of them). Initially it ran in about 20 minutes. And
on my development box with an exact copy of their database it still runs in
20 minutes. On the client's machine it now takes 2 hours 47 minutes and
pretty consistently so.

Obviously the 2 machines are different, the development box is a single
processor 850 mhz and 512 MB memory and the client machine is a quad
processor with 4 500 mhz's with 1 GB memory. Both machines are running SQL
Server 2000 and Windows 2000 server. The one other difference of note is
that the client's machine is using a hardware raid system. No one at the
client's site will admit to making any changes. And the machine is pretty
much dedicated to our process.

Using the task manager neither the processors nor the memory is pegged for
any significant period of time.

We have tried freeing up disk space and defragmentation. This resulted in a
net change of exactly zero. We have also raised the priority of SQL Server,
which also resulted in a net change of exactly zero. They were also using
Norton's virus thing across the network which we have turned off for a net
change of exactly zero. The stored procedures themselves have been pretty
well optimized for what they do. They are also in use other places without
this problem.

What I am looking for is something we may have missed and should look for.
Some way of testing some area that will show why it is taking so long. Some
things to try to help speed this thing up.

Thanks


-- B

Bradley,

a few candidate for checking:

1. statistics outdated?
2. hyperthreading turned on?
3. parallelism changed?

did the change happen suddenly or gradually? does the longer times relate
to larger data amount? Can the longer times be attributed to some
procedures or all the procedures run longer proportionally? You can use
Profiler and Perfmon the collect more info for the trouble shooting.

hope this can be some help.

Quentin


"Bradley M. Small" <BSmall@XNOSPAMXmjsi.com> wrote in message
news:OVrPsbd2DHA.540@tk2msftngp13.phx.gbl...

Well, I shrunk the database and truncated the transaction log and cut the
time back to about 1-1/2 hour. It is running raid 5. For now I am just happy
to see anything have some effect on the process. I am not sure if this will
be enough to satisfy the customer, I have them checking on their raid
controller settings.

For everyone that responded, thanks for all your help and suggestions

-- B

"Quentin Ran" <ab@who.com> wrote in message
news:euQ6Eyf2DHA.1336@TK2MSFTNGP12.phx.gbl...