SQL Server 2008 RTM (10.0.1600.22)
Running a typical index fragmentation report on a table:
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
s.index_type_desc,
s.avg_fragmentation_in_percent,
s.fragment_count,
s.page_count,
s.record_count
FROM
sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('RPT_XXX'), NULL, NULL, 'DETAILED') s
INNER JOIN
sys.indexes i ON i.OBJECT_ID = s.OBJECT_ID
AND i.index_id = s.index_id
and i.object_id = OBJECT_ID('RPT_XXX')
WHERE
s.avg_fragmentation_in_percent > 25
tells me that one index on that table has 7 fragments, 7 pages, about 700 rows and over 28% fragmentation. However, when I look at the index storage properties in the Management Studio, I get nearly 9000 pages, over 12 million rows, and 0.01% fragmentation, which are the expected numbers because the table is big and I just did the index rebuild and updated statistics on the table.
What, apart from a possible bug in the unsupported RTM version of SQL Server, might be the reasons for this discrepancy?
where sys.indexes.is_hypothetical=N'0'
but I guessdm_db_index_physical_stats()
wouldn't return anything for hypothetical indexes anyway). However, looking at that query helped me realize where the problem was. – mustaccio Oct 13 '15 at 19:47SQL Server 2008 RTM (10.0.1600.22)
. It's out of support already unless you patch it with latest SP. – Kin Shah Oct 13 '15 at 20:50