SQL Server – Why Database is Still Fragmented After Reorganizing and Rebuilding

indexperformancesql server

I created a Maintenace Plan, first i reorganize the indexes, then rebuild, and final update the statistics every Saturday.
I ran this script for verification:

SELECT 
    S.name as 'Schema',
    T.name as 'Table',
    I.name as 'Index',
    DDIPS.avg_fragmentation_in_percent,
    DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
    INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
    INNER JOIN sys.schemas S on T.schema_id = S.schema_id
    INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id 
        AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
    AND I.name is not null
    AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent DESC

But after the run, the fragmentation is still very high. Is that okay? What could be done?

Thank you for your help! 🙂

The result

Best Answer

Bottom line: fragmentation is irrelevant for small indexes. Never bother about it for indexes less than some 1000 pages, or perhaps we should say 10000 nowadays. If you remember cassette tapes, we always have some noise in the background (my analogy, perhaps works better in Swedish).

Tip1: don't use single quotes for column names in SELECT list. It divert from ANSI SQL and it is "weird". :-)

Tip2: you don't have to join all those tables. Use meta-data functions instead.

Tip3: Heaps don't have fragmentation in the same way as indexes do. So either filter out the heaps or learn about forwarding pointers/forwarded records and handle them separately from your indexes. This might be useful, for instance: https://karaszi.com/rebuild-all-fragmented-heaps.

Tip4: Jumping back and forth on disk isn't as costly as it used to be. Assuming you are on faster storage than old-school spinning disks, that is. I.e., that type of fragmentation might not hurt you much. There's still the aspect of read-ahead, though. See for instance this: http://sqlblog.karaszi.com/fragmentation-the-final-installment/.

Tip5: Use something more reasonable to filter on the fragmentation level, like > 5% instead of > 0%.

Here's a query, simplified and with my recommendations above. But, again, perhaps you shouldn't worry about jumping back and forth in the first place?

SELECT 
 OBJECT_SCHEMA_NAME(f.object_id) AS Schema_
,OBJECT_NAME(f.object_id) AS Table_
,i.name AS Index_
,f.avg_fragmentation_in_percent
,f.page_count
,i.type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS f
  INNER JOIN sys.indexes AS i On f.object_id = i.object_id
WHERE f.avg_fragmentation_in_percent > 5
 AND f.page_count > 1000
 AND i.type_desc NOT IN('HEAP', 'CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE')
ORDER BY avg_fragmentation_in_percent DESC
Related Question