-- Show a list of indexes' fragmentation stats.
SELECT object_name(object_id), DB_NAME(database_id) , * FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL)
WHERE database_id = 11
ORDER BY avg_fragmentation_in_percent DESC


-- Rebuild update the statistic, ReOrganize just re-arrange the pages but faster and can stop at anytime with quick recovery.
ALTER INDEX all ON Person.Address 
REBUILD    
WITH ( FILLFACTOR = 80 )    -- typical setting to avoid frequent page split which cause more fragmentation

To detect and fix the fragmentation

Fix index fragmentation

SQL