An Urgent help required on Performance tuning

Dear All,
We use SQL Server 2000. Inorder to increase the performance archived about
2.5 million records.
However after the archival, the performance is found to be reduced as you
can see in the below comparison details. This downsizing of almost 2.5
millions records has been followed by an "update statistics" in all table
from where, records were removed.
Can anybody give us hints : what could be the the potntial reason for
non-improvement of performance.
What all tunings are yet to be done?
( We are relatively new in this area)
Performance Performance
After Before
- To reset working status for a plant 57s
40s
- To get the menu of pre-release:
delay before having possibility to chose
plant and report for pre release 2 min 4s
2min 41s
- To select a plant 2 min 12s 2min 5s
- To pre-release the P&L2 of a plant 4 min 4s
5min 22s
Thanks and Regards
Sathian"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:eaakvo$nc6$1@.news4.fe.internet.bosch.com...
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
Have you done a backup and a trancate log on that db?|||Have you rebuilt your indexes?
Phil
"Sathian" wrote:

> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
> What all tunings are yet to be done?
>
> ( We are relatively new in this area)
>
>
> Performance Performance
> After Before
> - To reset working status for a plant 57s
> 40s
> - To get the menu of pre-release:
> delay before having possibility to chose
> plant and report for pre release 2 min 4s
> 2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
> 5min 22s
> Thanks and Regards
> Sathian
>
>|||It is not always a big surprise if performance does not improve when
old data is archived. If the tables were properly designed, the
indexes well chosen, and the queries done right, then databases can
often grow quite large without degrading performance, so it seems
reasonable that shrinking them back won't improve performance.
For performance to get worse when data is archived is unexpected.
Perhaps there was some other change at the same time that had a
significant impact.
One thing worth trying: don't just update statistics, rebuild the
indexes. See DBCC DBREINDEX in the documentation.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Jul 2006 20:40:27 +0530, "Sathian" <sathian.t@.in.bosch.com>
wrote:

>Dear All,
>We use SQL Server 2000. Inorder to increase the performance archived about
>2.5 million records.
>However after the archival, the performance is found to be reduced as you
>can see in the below comparison details. This downsizing of almost 2.5
>millions records has been followed by an "update statistics" in all table
>from where, records were removed.
>
>Can anybody give us hints : what could be the the potntial reason for
>non-improvement of performance.
>What all tunings are yet to be done?
>
>( We are relatively new in this area)
>
>
>Performance Performance
>After Before
> - To reset working status for a plant 57s
>40s
> - To get the menu of pre-release:
>delay before having possibility to chose
>plant and report for pre release 2 min 4s
>2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
>5min 22s
>Thanks and Regards
>Sathian
>|||As others have indicated, you need to examine your indexes. What
percentage of your data did the archiving process represent?
Sathian wrote:
> Dear All,
> We use SQL Server 2000. Inorder to increase the performance archived about
> 2.5 million records.
> However after the archival, the performance is found to be reduced as you
> can see in the below comparison details. This downsizing of almost 2.5
> millions records has been followed by an "update statistics" in all table
> from where, records were removed.
>
> Can anybody give us hints : what could be the the potntial reason for
> non-improvement of performance.
> What all tunings are yet to be done?
>
> ( We are relatively new in this area)
>
>
> Performance Performance
> After Before
> - To reset working status for a plant 57s
> 40s
> - To get the menu of pre-release:
> delay before having possibility to chose
> plant and report for pre release 2 min 4s
> 2min 41s
> - To select a plant 2 min 12s 2min 5s
> - To pre-release the P&L2 of a plant 4 min 4s
> 5min 22s
> Thanks and Regards
> Sathian|||On Thu, 27 Jul 2006 20:40:27 +0530, "Sathian" <sathian.t@.in.bosch.com>
wrote:
>Can anybody give us hints : what could be the the potntial reason for
>non-improvement of performance.
Can you show a little SQL that's running slowly?
It may be the new data is not well-distributed, so after deleting the
old data, the indexes look even worse to the optimizer, and even worse
plans are chosen. Stranger things have happened.
J.sql