Index defragmentation
Query whether index fragmentation occurs
SELECT object_name(dt.object_id) Tablename,si.name Indexname,dt.avg_fragmentation_in_percent AS ExternalFragmentation,
dt.avg_page_space_used_in_percent AS internalFragmentation
FROM
(
SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (db_id('SmartTRMUAES'),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_percent10 indicates that external fragmentation occurs in the corresponding index;
2) Value of InternalFragmentation
