Showing posts with label idea. Show all posts
Showing posts with label idea. Show all posts

Friday, March 30, 2012

Index Statistics Degredation

Any idea why my index statistics seem to degenerate so quickly.
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritten.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the complex
query runs in about 4 seconds. This can be repeated throughout the day, where
the query degrades and then I run update statistics [tablename] with fullscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>

Index Statistics Degredation

Any idea why my index statistics seem to degenerate so quickly.
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritte
n.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the com
plex
query runs in about 4 seconds. This can be repeated throughout the day, wher
e
the query degrades and then I run update statistics [tablename] with ful
lscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.c...agmentation.asp
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>sql

Index Statistics Degredation

Any idea why my index statistics seem to degenerate so quickly.
I am running SQL 2000, SP4 on Windows 2003.
I have a complex query with approximately 15 joins, and several of those
joins are joining on sub queries and temporary tables.
While I recognize there is some needed optimization that needs to be done in
rewriting this statement, I hope that you can address my initial question,
rather than perhaps stating the obvious that this query needs to be rewritten.
I run a dbcc dbreindex on all fragmented indexes and then run update
statistics [tablename] with fullscan on all tables.
After doing so, this complex query runs in about 4 seconds. After two hours
or less, I run it again, and it runs in about 90 seconds. If I then run
update statistics [tablename] with fullscan on all tables again, the complex
query runs in about 4 seconds. This can be repeated throughout the day, where
the query degrades and then I run update statistics [tablename] with fullscan
and it is back to an acceptable return time.
If I just run sp_UpdateStats, there is no change in performance, only when I
run update statistics [tablename] with fullscan.
So what I am seeing is apparently, a degredation in statistics. What would
cause my statistics to degredate so quickly?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1Have a read of this article to better understand Index degradation.
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:693bc6602e52e@.uwe...
> Any idea why my index statistics seem to degenerate so quickly.
> I am running SQL 2000, SP4 on Windows 2003.
> I have a complex query with approximately 15 joins, and several of those
> joins are joining on sub queries and temporary tables.
> While I recognize there is some needed optimization that needs to be done
> in
> rewriting this statement, I hope that you can address my initial question,
> rather than perhaps stating the obvious that this query needs to be
> rewritten.
>
> I run a dbcc dbreindex on all fragmented indexes and then run update
> statistics [tablename] with fullscan on all tables.
> After doing so, this complex query runs in about 4 seconds. After two
> hours
> or less, I run it again, and it runs in about 90 seconds. If I then run
> update statistics [tablename] with fullscan on all tables again, the
> complex
> query runs in about 4 seconds. This can be repeated throughout the day,
> where
> the query degrades and then I run update statistics [tablename] with
> fullscan
> and it is back to an acceptable return time.
> If I just run sp_UpdateStats, there is no change in performance, only when
> I
> run update statistics [tablename] with fullscan.
> So what I am seeing is apparently, a degredation in statistics. What would
> cause my statistics to degredate so quickly?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

Friday, February 24, 2012

Index Corruption

Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor MauricioFrequent corruption is not normal. You would want to check
your hardware and the windows event logs for any indications
of hardware issues.
You can identify issues using dbcc checkdb. If you want to
limit the check to specific tables, you dbcc checktable.
The best approach to fixing the issues is to do root cause
analysis and determine what is causing the corruption and
then address those issues.
-Sue
On Mon, 18 Oct 2004 23:01:02 -0300, "Vitor Mauricio de N.
Silva" <vitor_mauricio@.newsgroup.nospam> wrote:
>Hi,
>What's the best approach to identify and fix index corruption problems. I
>have some tables that corruption is occuring frequently.
>Any idea ?
>Vitor Mauricio
>|||Hi Vitor Mauricio,
I wanted to post a quick note to see if you would like additional
assistance or information regarding the information Sue had sent to you. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Corruption could happen if you are performing a few operations on a
given table in batch without commiting them. Say for instance you
delete a bunch of records, and perform an insert, then update a field.
If you are doing this in a batch, then you could possibly run into a
corruption issue due to the fact that the datbase hasnt committed the
activity. The index "thinks" that there are more records there then
there actually are, so in this case the index becomes corrupt and you
must re-build it. One way around this is to separate your logic into
more compartmentalized code or update statistics between operations.
Vitor Mauricio de N. Silva wrote:
> *Hi,
> What's the best approach to identify and fix index corruption
> problems. I
> have some tables that corruption is occuring frequently.
> Any idea ?
> Vitor Mauricio *
mbroaddus
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1165249.html

Index Corruption

Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor Mauricio
Frequent corruption is not normal. You would want to check
your hardware and the windows event logs for any indications
of hardware issues.
You can identify issues using dbcc checkdb. If you want to
limit the check to specific tables, you dbcc checktable.
The best approach to fixing the issues is to do root cause
analysis and determine what is causing the corruption and
then address those issues.
-Sue
On Mon, 18 Oct 2004 23:01:02 -0300, "Vitor Mauricio de N.
Silva" <vitor_mauricio@.newsgroup.nospam> wrote:

>Hi,
>What's the best approach to identify and fix index corruption problems. I
>have some tables that corruption is occuring frequently.
>Any idea ?
>Vitor Mauricio
>
|||Hi Vitor Mauricio,
I wanted to post a quick note to see if you would like additional
assistance or information regarding the information Sue had sent to you. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Corruption could happen if you are performing a few operations on a given table in batch without commiting them. Say for instance you delete a bunch of records, and perform an insert, then update a field. If you are doing this in a batch, then you could possibly run into a corruption issue due to the fact that the datbase hasnt committed the activity. The index "thinks" that there are more records there then there actually are, so in this case the index becomes corrupt and you must re-build it. One way around this is to separate your logic into more compartmentalized code or update statistics between operations.

Quote:

Originally posted by Vitor Mauricio de N. Silva
Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor Mauricio

Index Corruption

Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor MauricioFrequent corruption is not normal. You would want to check
your hardware and the windows event logs for any indications
of hardware issues.
You can identify issues using dbcc checkdb. If you want to
limit the check to specific tables, you dbcc checktable.
The best approach to fixing the issues is to do root cause
analysis and determine what is causing the corruption and
then address those issues.
-Sue
On Mon, 18 Oct 2004 23:01:02 -0300, "Vitor Mauricio de N.
Silva" <vitor_mauricio@.newsgroup.nospam> wrote:

>Hi,
>What's the best approach to identify and fix index corruption problems. I
>have some tables that corruption is occuring frequently.
>Any idea ?
>Vitor Mauricio
>|||Hi Vitor Mauricio,
I wanted to post a quick note to see if you would like additional
assistance or information regarding the information Sue had sent to you. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Corruption could happen if you are performing a few operations on a
given table in batch without commiting them. Say for instance you
delete a bunch of records, and perform an insert, then update a field.
If you are doing this in a batch, then you could possibly run into a
corruption issue due to the fact that the datbase hasnt committed the
activity. The index "thinks" that there are more records there then
there actually are, so in this case the index becomes corrupt and you
must re-build it. One way around this is to separate your logic into
more compartmentalized code or update statistics between operations.
Vitor Mauricio de N. Silva wrote:
> *Hi,
> What's the best approach to identify and fix index corruption
> problems. I
> have some tables that corruption is occuring frequently.
> Any idea ?
> Vitor Mauricio *
mbroaddus
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1165249.html