Your hero for the day!
Did you know that you can’t defrag an index with < 8 pages?
After reading this…now you do. This was some obscure information that I never really thought about until I was analyzing some indexes on our system. I have reports sent to me nightly that check for fragmentation and I noticed that a handful were consistently staying fragmented.
This is simply because SQL server will not defrag anything with less than 8 pages. Without doing any research on the matter my assumption is that this has to do with extents being 8 pages (feel free to add to this concept while I try to find some google time).
I have modified the index fragmentation script to now disregard anything with less than 8 pages. This will hopefully prevent anyone else from being stumped for a bit.
SELECT DISTINCT
sc.name AS [Schema Name],
DB_NAME(ps.database_id) AS [Database Name],
OBJECT_NAME(ps.object_id) AS [Table Name],
ix.name AS [Index Name],
ps.avg_fragmentation_in_percent AS [Frag %],
ps.page_count AS [Page Count],
ix.is_disabled AS [Disabled],
ix.is_hypothetical AS [Hypothetical],
ix.fill_factor,
CASE
WHEN ps.avg_fragmentation_in_percent > 30 AND ix.type 0 AND ix.name IS NOT NULL
THEN 'ALTER INDEX ' + ix.name + ' ON ' + DB_NAME(ps.database_id) + '.' + sc.name + '.' + OBJECT_NAME(ps.object_id) + ' REBUILD'
WHEN ps.avg_fragmentation_in_percent < 30 AND ix.type 0 AND ix.name IS NOT NULL THEN 'ALTER INDEX ' + ix.name + ' ON ' + DB_NAME(ps.database_id) + '.' + sc.name + '.' + OBJECT_NAME(ps.object_id) + ' REORGANIZE' ELSE 'ALTER TABLE ' + DB_NAME(ps.database_id)+ '.' + sc.name + '.' + OBJECT_NAME(ps.object_id) + ' REBUILD' END AS [Fix TSQL] FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL, NULL) AS ps INNER JOIN sys.tables tb ON tb.object_id = ps.object_id INNER JOIN sys.schemas sc ON tb.schema_id = sc.schema_id INNER JOIN sys.indexes ix ON ps.object_id = ix.object_id AND ps.index_id = ix.index_id WHERE ps.database_id = DB_ID() AND avg_fragmentation_in_percent > 5 AND ps.page_count > 8
ORDER BY page_count DESC
--OPTION (MAXDOP 1)