Advanced Search

Fix index fragmentation

/ Published in: SQL
Save to your folder(s)
To detect and fix the fragmentation

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.