Technical Article

Check Fragmentation on All Indexes on Database

,

Copy and paste the script in the database to review

SELECT sysobj.name object_name, 
 sysobj.xtype object_type, 
 indexes.name index_name, 
 index_data.database_id, 
    index_data.object_id, 
    index_data.index_id, 
    index_data.avg_fragmentation_in_percent, 
    index_data.avg_fragment_size_in_pages,
    index_data.avg_page_space_used_in_percent,
    index_data.record_count
Into #fragmentados 
FROM sys.dm_db_index_physical_stats (6, NULL,NULL, NULL,'SAMPLED') index_data -- Review all tables on database
inner join 
sys.sysobjects sysobj 
on index_data.object_id = sysobj.id 
left outer join sys.sysindexes indexes 
on index_data.index_id = indexes.indid 
and index_data.object_id = indexes.id
WHERE (avg_fragmentation_in_percent > 10
OR avg_page_space_used_in_percent < 90)
-- avg_fragmentation_in_percent (sys.dm_db_index_physical_stats) / logical scan fragmentation (dbcc showcontig) < 10%
-- avg_fragment_size_in_page (sys.dm_db_index_physical_stats) / Extent Scan Fragmentation (dbcc showcontig)
-- avg_page_space_used_in_percent (sys.dm_db_index_physical_stats) / Avg. Page Density (dbcc showcontig) > 90 %

Rate

3.33 (9)

You rated this post out of 5. Change rating

Share

Share

Rate

3.33 (9)

You rated this post out of 5. Change rating