B
Bradley M. Small
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
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