Here’s quick way to find Tables that have heavily fragmented indexes. This is designed to find tables that are above 70% fragmentation. It’s a good idea to incorporate this into a regularly scheduled maintenance job, and just REBUILD those that are above 70% to 80% fragmentation. Anything below this point can simply be REORGANIZED.
select 'database_name' = db_name(sddips.database_id) , 'table_name' = object_name(sddips.object_id) , 'object type' = ist.table_type , 'fragmentation' = left(sddips.avg_fragmentation_in_percent, 4) from sys.dm_db_index_physical_stats (db_id(), null, null, null, null) sddips join information_schema.TABLES ist on object_name(sddips.object_id) = ist.table_name where ist.table_type = 'base table' and sddips.avg_fragmentation_in_percent > 70 order by sddips.avg_fragmentation_in_percent desc;
Categories: Agent Jobs, Alerts, Automations, Backup & Restore, Command Prompt, Database Mail, Database Mirroring, ELI5, Error Solutions, Featured, Folder System, Forensics & Auditing, High Availability, Long Scripts, Management Studio, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, TSQL, Work Arounds