Copy and paste the script in the database to review
2008 Index Fragmentation Maintenance
Just a stab at automating index maintainenance in SQL Server 2008
2013-01-11 (first published: 2008-11-07)
2,573 reads
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 %