Using SQL server, If i navigate to a table in the object explorer, go to its indexes, go to its properties, and view the fragmentation tab, the top section has 2 lines for me, the Page fullness in percent, and the Total Fragmentation in percent.
I also have a query i occasional use for index information. Trimming out some unrelated fields, this is the query:
SELECT t.name, ix.name, avg_fragmentation_in_percent
FROM sys.indexes AS ix
INNER JOIN sys.tables t ON t.object_id = ix.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, 'DETAILED') ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
WHERE ix.index_id > 0
AND ix.name IS NOT NULL
When i run this query, i get 3 columns, the first 2 pointing to the index itself, and for this query, the third is a fragmentation in percent for that index.
These 2 numbers are Usually they are very close, sometimes identical, but it also can be and frequently is different. I have had cases where the discrepancy is massive, over 50% in some cases, but normally it is something like <1% vs 15-30%. The queries result is almost always the higher percent.
Rebuilding the index will completely or nearly zero both of these numbers (though not necessarily will both be zero). So i now have 2 numbers for fragmentation on an index. According to the documentation for sys.dm_db_index_physical_stats and Index Properties(FragmentationPage) Both are supposed to be the logical Fragmentation of the index, so they should always be the same number. How is it that i am getting different numbers for what should be the same statistic?