FRAGMENTATION INFORMATION FOR A DATABASE WITH PAGE COUNTS AND AVG_FRAG

Script

SELECT object_name(object_id) as objectname, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('Databasename'),
null, NULL, NULL, NULL)
where avg_fragmentation_in_percent >30 and page_count >1000

Note:please replace the database

Generally the page count > 1000 and avg_fragmentation_in_percent>30 indicates there was a performance degrade