How to know whether index fragmentation has occurred or not?

736 View    Mar 4 2020 2:43PM

SELECT object_name(dt.object_id) Tablename,

IndexName,dt.avg_fragmentation_in_percent AS

ExternalFragmentation,dt.avg_page_space_used_in_percent AS




    SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent

    FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'


WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id

AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10

AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC

Replace the database name 'AdventureWorks' with the target database name in the following query

·         ExternalFragmentation value > 10 indicates external fragmentation occurred for the corresponding index

·         InternalFragmentation value < 75 indicates internal fragmentation occurred for the corresponding index


Solution : Rebuild all index