Index Fragmentation -- Help!

[Robert Bollinger]

Real quick question here.

If you run this code against a database. Example:

use MyDatabase

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20

it gives you the list of fragmented indexes.

If you then rebuild the indexes and run the same code again . . .
IT GIVES ME THE SAME RESULTS!!!

Do you know what's going on???

Thanks for your help!

Bob B

[Erland Sommarskog]

Post by Robert Bollinger
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
it gives you the list of fragmented indexes.
If you then rebuild the indexes and run the same code again . . .
IT GIVES ME THE SAME RESULTS!!!

You need to filter this query to remove the noise.

You have choosen DETAILED. This means that you also get the upper levels
of the tree. No one cares if they are fragmented, because they are never
scannned.

Furthermore, fragmentation on small tables is also nothing to be worried
about. As long as the table is entirely in cache, fragementation is a non-
issue, as this is something which matters when reading from spinning
disks. Thus, normally this query is filtered for page_count > 1000.

Personally, I find the page density more interesting, because this if
this this value is low, this means that your buffer cache includes a
lot of unused space, and may increase your overall I/O. I don't recall
what page density is called as a column, but it is only populated for
DETAILED.

[Robert Bollinger]

So basically nothing has changed after the rebuild.

I figured that if you rebuilt ALL indexes that statistics would also update and there would be 0 fragmentation. Apparently all indexes are not rebuilt with the rebuild option.

Is there a way to apply a filter so that it only shows the indexes that affect performance? When I ran the query the first time it looked like an index rebuild had never been done. Some indexes still show avg_fragmentation_in_percent at 100%.

Thanks for responding.

RBollinger

[Erland Sommarskog]

Post by Robert Bollinger
So basically nothing has changed after the rebuild.
I figured that if you rebuilt ALL indexes that statistics would also
update and there would be 0 fragmentation. Apparently all indexes are
not rebuilt with the rebuild option.

Oh, they were certainly rebuilt in the meaning that SQL Server moved the
data from one place or another. But that does not necessarily not remove
all fragmentation, at least not as you have tables in mixed extents.

Post by Robert Bollinger
Is there a way to apply a filter so that it only shows the indexes that
affect performance?

How would that be possible? You can have 1 TB table which is grossly
fragmented, but this has no impact at all, because it is never scanned;
all access is by point lookup.

But if you apply the filters that I suggested in my post, you will get
rid of most of the noise.