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
>