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.
Showing posts with label increase. Show all posts
Showing posts with label increase. 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.sql
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
Monday, February 13, 2012
Always the Worker Thread
I received the following msg resulting from a backup: (partial) "increase the
maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
the maximum number of databases to backup concurrently". There is a another
full backup at a different time of day on this server that succeeds. This
instance is on a cluster server. I do not receive this msg throughout the
day. I do have 400 or so connections throughout the day. My threads are set
to the default of 255. I have 4 CPUs in each server. How can I accommodate
this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
time the backup is occurring?
One other question on the dbcc sqlperf(UMSSTATS). So my "work queued" at 0
or 1 and idle workers should be close to the same number as num workers?
thanks
"RomM" wrote:
> I received the following msg resulting from a backup: (partial) "increase the
> maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
> the maximum number of databases to backup concurrently". There is a another
> full backup at a different time of day on this server that succeeds. This
> instance is on a cluster server. I do not receive this msg throughout the
> day. I do have 400 or so connections throughout the day. My threads are set
> to the default of 255. I have 4 CPUs in each server. How can I accommodate
> this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
> time the backup is occurring?
>
maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
the maximum number of databases to backup concurrently". There is a another
full backup at a different time of day on this server that succeeds. This
instance is on a cluster server. I do not receive this msg throughout the
day. I do have 400 or so connections throughout the day. My threads are set
to the default of 255. I have 4 CPUs in each server. How can I accommodate
this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
time the backup is occurring?
One other question on the dbcc sqlperf(UMSSTATS). So my "work queued" at 0
or 1 and idle workers should be close to the same number as num workers?
thanks
"RomM" wrote:
> I received the following msg resulting from a backup: (partial) "increase the
> maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
> the maximum number of databases to backup concurrently". There is a another
> full backup at a different time of day on this server that succeeds. This
> instance is on a cluster server. I do not receive this msg throughout the
> day. I do have 400 or so connections throughout the day. My threads are set
> to the default of 255. I have 4 CPUs in each server. How can I accommodate
> this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
> time the backup is occurring?
>
Always the Worker Thread
I received the following msg resulting from a backup: (partial) "increase the
maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
the maximum number of databases to backup concurrently". There is a another
full backup at a different time of day on this server that succeeds. This
instance is on a cluster server. I do not receive this msg throughout the
day. I do have 400 or so connections throughout the day. My threads are set
to the default of 255. I have 4 CPUs in each server. How can I accommodate
this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
time the backup is occurring?One other question on the dbcc sqlperf(UMSSTATS). So my "work queued" at 0
or 1 and idle workers should be close to the same number as num workers?
thanks
"RomM" wrote:
> I received the following msg resulting from a backup: (partial) "increase the
> maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
> the maximum number of databases to backup concurrently". There is a another
> full backup at a different time of day on this server that succeeds. This
> instance is on a cluster server. I do not receive this msg throughout the
> day. I do have 400 or so connections throughout the day. My threads are set
> to the default of 255. I have 4 CPUs in each server. How can I accommodate
> this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
> time the backup is occurring?
>
maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
the maximum number of databases to backup concurrently". There is a another
full backup at a different time of day on this server that succeeds. This
instance is on a cluster server. I do not receive this msg throughout the
day. I do have 400 or so connections throughout the day. My threads are set
to the default of 255. I have 4 CPUs in each server. How can I accommodate
this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
time the backup is occurring?One other question on the dbcc sqlperf(UMSSTATS). So my "work queued" at 0
or 1 and idle workers should be close to the same number as num workers?
thanks
"RomM" wrote:
> I received the following msg resulting from a backup: (partial) "increase the
> maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
> the maximum number of databases to backup concurrently". There is a another
> full backup at a different time of day on this server that succeeds. This
> instance is on a cluster server. I do not receive this msg throughout the
> day. I do have 400 or so connections throughout the day. My threads are set
> to the default of 255. I have 4 CPUs in each server. How can I accommodate
> this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
> time the backup is occurring?
>
Always the Worker Thread
I received the following msg resulting from a backup: (partial) "increase th
e
maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
the maximum number of databases to backup concurrently". There is a anothe
r
full backup at a different time of day on this server that succeeds. This
instance is on a cluster server. I do not receive this msg throughout the
day. I do have 400 or so connections throughout the day. My threads are se
t
to the default of 255. I have 4 CPUs in each server. How can I accommodat
e
this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the sam
e
time the backup is occurring?One other question on the dbcc sqlperf(UMSSTATS). So my "work queued" at 0
or 1 and idle workers should be close to the same number as num workers?
thanks
"RomM" wrote:
> I received the following msg resulting from a backup: (partial) "increase
the
> maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
> the maximum number of databases to backup concurrently". There is a anot
her
> full backup at a different time of day on this server that succeeds. This
> instance is on a cluster server. I do not receive this msg throughout the
> day. I do have 400 or so connections throughout the day. My threads are
set
> to the default of 255. I have 4 CPUs in each server. How can I accommod
ate
> this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the s
ame
> time the backup is occurring?
>
e
maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
the maximum number of databases to backup concurrently". There is a anothe
r
full backup at a different time of day on this server that succeeds. This
instance is on a cluster server. I do not receive this msg throughout the
day. I do have 400 or so connections throughout the day. My threads are se
t
to the default of 255. I have 4 CPUs in each server. How can I accommodat
e
this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the sam
e
time the backup is occurring?One other question on the dbcc sqlperf(UMSSTATS). So my "work queued" at 0
or 1 and idle workers should be close to the same number as num workers?
thanks
"RomM" wrote:
> I received the following msg resulting from a backup: (partial) "increase
the
> maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
> the maximum number of databases to backup concurrently". There is a anot
her
> full backup at a different time of day on this server that succeeds. This
> instance is on a cluster server. I do not receive this msg throughout the
> day. I do have 400 or so connections throughout the day. My threads are
set
> to the default of 255. I have 4 CPUs in each server. How can I accommod
ate
> this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the s
ame
> time the backup is occurring?
>
Subscribe to:
Posts (Atom)