Showing posts with label urgent. Show all posts
Showing posts with label urgent. Show all posts

Tuesday, March 20, 2012

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.

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

Saturday, February 25, 2012

AMO: refresh DSV and accept changes..

I tried the sql server OLAP newsgroup, but without success... this is fairly urgent, so will try in here:
I am stumped on this, how can I programmatically do what can be done in
Visual Studio by:
1. Right clicking inside datasource view
2. Choosing refresh
3. Accepting the changes

I am trying to get new fact table columns into the DSV, obviously
without doing the manual steps above.

I have tried dsv.Refresh() and dsv.Update() but it is not as simple as
that or the sequencing is incorrect (or, most likely, I am looking in
entirely the wrong place).

Thanks for any help.

There is no programmatiic way to do refresh on DSV. However, You can use data adapter to fill schema and get a new datatable and merge the table yourself.

For adding a column, you can use data adapter to do FillSchema() with the table you want to refresh and add the new column into existing table. Finally, call DSV.Update().

|||Thanks for the response - this was pretty much the only way we could solve this as well.... but why isn't the method that MS uses to do this exposed for our use as well? It seems like they have a pretty efficient way of doing this. Nearly everything else allows you to "script the action", why not this?
|||This refresh of DSV from relational DB is part of the execution of DSV but not on AMO. Although we found that it is useful if we move the code to AMO, it is to late of the cycle. Since it has work around, so we cut this feature. We will consider it in next release. Thanks