Posted By

wilson0825 on 11/11/13


Tagged

index fragmentation


Versions (?)

Fix index fragmentation


 / Published in: SQL
 

To detect and fix the fragmentation

  1. -- Show a list of indexes' fragmentation stats.
  2. SELECT object_name(object_id), DB_NAME(database_id) , * FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL)
  3. WHERE database_id = 11
  4. ORDER BY avg_fragmentation_in_percent DESC
  5.  
  6.  
  7. -- Rebuild update the statistic, ReOrganize just re-arrange the pages but faster and can stop at anytime with quick recovery.
  8. ALTER INDEX ALL ON Person.Address
  9. REBUILD
  10. WITH ( FILLFACTOR = 80 ) -- typical setting to avoid frequent page split which cause more fragmentation

Report this snippet  

You need to login to post a comment.