We have moved our system from an old server to a new, faster and in
all respects 'better' server. And everything performs mush better with
the exception of one of our SQL PL procedures.

The old system was a Windows 2003 enterprise server, 32-bits, with 3
GB of memory, and 2 cpu's. With DB2 9.5 Enterprise Edition, 32 bits,
fixpack 4a. With the exception of backup and logs everything was
stored on one physical disk.

The new system is a Windows 2003 Enterprise server, 64 bits, 32 GB of
memory and 2 cpu's. DB2 9.5 Enterprise edition, 64 bits, fixpack 4a.
Now all is stored on a san disk system, with 5 parallel disks for
data, 2 for logs and another 2 for backups and archive logs.

The result for one of our SQL procedures is that the execution time
has changed from about 20 minutes to 4 hours!! I have run design
advisor and no extra indexes were necessary.

Now I don't know where to start looking for the bottleneck. Could
someone help. please :-)

Regards
Odd Bjørn Andersen,
ErgoGroup AS, Oslo, Norway

On Oct 29, 10:29 am, oddbande <odd.ander... at (no spam) ergo.no> wrote:



We have moved our system from an old server to a new, faster and in
all respects 'better' server. And everything performs mush better with
the exception of one of our SQL PL procedures.

The old system was a Windows 2003 enterprise server, 32-bits, with 3
GB of memory, and 2 cpu's. With DB2 9.5 Enterprise Edition, 32 bits,
fixpack 4a. With the exception of backup and logs everything was
stored on one physical disk.

The new system is a Windows 2003 Enterprise server, 64 bits, 32 GB of
memory and 2 cpu's. DB2 9.5 Enterprise edition, 64 bits, fixpack 4a.
Now all is stored on a san disk system, with 5 parallel disks for
data, 2 for logs and another 2 for backups and archive logs.

The result for one of our SQL procedures is that the execution time
has changed from about 20 minutes to 4 hours!! I have run design
advisor and no extra indexes were necessary.

Now I don't know where to start looking for the bottleneck. Could
someone help. please :-)

Regards
Odd Bjørn Andersen,
ErgoGroup AS, Oslo, Norway

Maybe the new architecture made the optimizer choose a different
(apparently less optimal) access path for parts of your procedure. Do
you still have the old environment available so that you can compare
the access plans for the different parts in your procedure?
--
Frederik Engelen

On Oct 29, 4:55 pm, Frederik Engelen <engelenfrede... at (no spam) gmail.com
wrote:





On Oct 29, 10:29 am, oddbande <odd.ander... at (no spam) ergo.no> wrote:

We have moved our system from an old server to a new, faster and in
all respects 'better' server. And everything performs mush better with
the exception of one of our SQL PL procedures.

The old system was a Windows 2003 enterprise server, 32-bits, with 3
GB of memory, and 2 cpu's. With DB2 9.5 Enterprise Edition, 32 bits,
fixpack 4a. With the exception of backup and logs everything was
stored on one physical disk.

The new system is a Windows 2003 Enterprise server, 64 bits, 32 GB of
memory and 2 cpu's. DB2 9.5 Enterprise edition, 64 bits, fixpack 4a.
Now all is stored on a san disk system, with 5 parallel disks for
data, 2 for logs and another 2 for backups and archive logs.

The result for one of our SQL procedures is that the execution time
has changed from about 20 minutes to 4 hours!! I have run design
advisor and no extra indexes were necessary.

Now I don't know where to start looking for the bottleneck. Could
someone help. please :-)

Regards
Odd Bjørn Andersen,
ErgoGroup AS, Oslo, Norway

Maybe the new architecture made the optimizer choose a different
(apparently less optimal) access path for parts of your procedure. Do
you still have the old environment available so that you can compare
the access plans for the different parts in your procedure?
--
Frederik Engelen

Try doing  a runstats with detailed indexes option on the tables which
is been used by Stored Proc and rebind the package of SP
This may help- Hide quoted text -

- Show quoted text -