July 29, 2010 at 12:12 pm
i started a process to dump the index fragmentation data into a common data warehouse and noticed that i have a lot of indexes where the fragmentation is over 50% on a daily basis. next I checked the table that stores all the alter index data and these indexes are rebuilt on a daily basis as well.
first i thought it may be a case of a small table, but i've noticed this on tables with over 1000000 rows as well. on one server with SQL 2005 Enterprise where we rebuild online i rebuilt one of the indexes offline and the next day it wasn't fragmented. I also noticed this on SQL 2005 Standard servers we have.
is there any reason for this? could it be data corruption?
July 29, 2010 at 1:29 pm
Data corruption causes high-severity error messages and possibly inaccessible databases.
I assume all the indexes in question have lots of pages (say > 1000)?
Autoshrink enabled?
Manual shrink running?
Clustered index on a guid and lots of inserts? (I've seen a large table with the cluster on a GUID go from 0 - 99.75% fragmentation in under 1 day)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2010 at 11:54 am
no auto shrink, very rarely do we do manual shrinks and no GUID clustered indexes or PK's
and i'm seeing it on indexes and tables with tens of thousands of pages
one possible theory is that the clustered index may not be on the most optimal column. and in a few cases when i rebuilt the indexes offline the problem seems to have gone away for that index the next day. as a stop gap i'm looking at changing our index maint routine to rebuild anything with a lower row count offline or everything offline
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply