Fragment an Index?

Some time ago i wrote a script for defraging indexes on sql server, this one will do the trick to! The old script looked at the sys.indexes table, where the information about fragmentation levels was not being used. The script below does.


I have set the maximum percentage of fragmentation to 30%, which is low enough. Getting a lower fragmentation percentage could be used maybe once of twice on a certain index, but for the most part it would only cost time and wouldn't give you that much of a boost on your indexes. 


I suggest to run this script and use outcome in the generated SQL columns at least once a weak. The first time it might hurt a bit, especially on long running tables. I had to use this one today on a 10.000.000 records counting table and it helped a lot in performance gains and only cost about 30 minutes or so. 


When you don't want to run ALL the generated SQL statements, but only the ones that have a high level of fragmentation look at the columns 'page_count' and 'avg_fragmentation_in_percent'. The higher both are, the more you want to fragment these. On the indexes i had to fragment today some where 50.000 pages with a 98% fragmentation (!?).


Like i said, you have to run the outcome of this query, preferable the 'statement_of_choice' column should be used. The fragmentation levels below 30% will result automatically into a reorganize in stead of a rebuild. That column could be used in my script from a while ago, where the statements were executed. 




declare @database_id int


select @database_id = database_id
from sys.databases
where name = db_name()


select distinct
o.name
, i.name
, s.avg_fragmentation_in_percent
, s.avg_fragment_size_in_pages
, s.page_count
, case
when avg_fragmentation_in_percent < 31 then 'reorganize'
else 'rebuild'
end as choise
, case
when avg_fragmentation_in_percent < 31 then replace(replace('alter index [[INDEXNAME]] on [OBJECTNAME] reorganize', '[INDEXNAME]', i.name), '[OBJECTNAME]', o.name)
else replace(replace('alter index [[INDEXNAME]] on [OBJECTNAME] rebuild', '[INDEXNAME]', i.name), '[OBJECTNAME]', o.name)
end as choise
, replace(replace('alter index [[INDEXNAME]] on [OBJECTNAME] rebuild', '[INDEXNAME]', i.name), '[OBJECTNAME]', o.name) as statement_rebuild
, replace(replace('alter index [[INDEXNAME]] on [OBJECTNAME] reorganize', '[INDEXNAME]', i.name), '[OBJECTNAME]', o.name) as statement_reorganize
, replace(replace(replace('dbcc indexdefrag([DATABASE_ID], ''[OBJECTNAME]'', ''[[INDEXNAME]]'')'
, '[DATABASE_ID]', @database_id)
, '[INDEXNAME]', i.name)
, '[OBJECTNAME]', o.name) as statement_defrag
from sys.dm_db_index_physical_stats(@database_id, null, null, null, null) s
inner join sys.tables o
on o.object_id = s.object_id
inner join sys.indexes i
on i.object_id = o.object_id
inner join sys.index_columns c
on c.object_id = o.object_id
and c.index_id = i.index_id
inner join sys.columns u
on u.column_id = c.column_id
and u.object_id = o.object_id
--order by 5 desc, s.avg_fragmentation_in_percent desc
order by 3, 1