Reorganizing/Rebuilding an index seems to have no effect

We have multiple database, one per customer with the same schema, and we
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database ArchitectSounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM|||Why are the queries so sensitive to fragmentation? Are they scanning the
table? If so, have you done anything about tuning them?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23WOqawZzHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Nope, it has a clustered index. The reorganize/rebuild works fine on the
> table in one database, but does nothing in another database.
> MTM
>|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM|||If you're doing a defrag , don't forget the statistics update
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OJSaclJ1HHA.1204@.TK2MSFTNGP03.phx.gbl...
> I'm going to re-examine this particular issue after we have updated the
> schema which will be sometime in October.
> Thanks.
> MTM
>