|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
An Urgent help required on Performance tuningWe 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
Show quote
"Sathian" <sathia***@in.bosch.com> wrote in message Have you done a backup and a trancate log on that db?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 rebuilt your indexes?
Phil Show quote "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 Show quote On Thu, 27 Jul 2006 20:40:27 +0530, "Sathian" <sathia***@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: Show quote > 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" <sathia***@in.bosch.com> Can you show a little SQL that's running slowly?wrote: >Can anybody give us hints : what could be the the potntial reason for >non-improvement of performance. 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.  |
|||||||||||||||||||||||
Other interesting topics