DECLARE @AVG_FRAG float
DECLARE @MIN_PAGES int
SET @AVG_FRAG = 10
SET @MIN_PAGES = 1000
SELECT db.name, ips.index_type_desc, COUNT(*) as FragmentedIndexes
FROM sys.dm_db_index_physical_stats(null, null, NULL, NULL, NULL) ips
left join sys.databases db on ips.database_id = db.database_id
WHERE avg_fragmentation_in_percent > @AVG_FRAG and
ips.page_count > @MIN_PAGES
group by db.name, index_type_desc
order by db.name, index_type_desc
Lists the databases containing indexes and heaps with an average fragmentation percentage larger than @AVG_FRAG and how many indexes in the given database that are fragmented. Used to identify index issues at the instance/server level. @MIN_PAGES define the minimum size of the index. Small indexes are usually not affected by fragmentation and you can safely ignore them. The default limit in the script is set to 1000 pages (8000KiB), based on the recommendations from http://technet.microsoft.com/en-us/library/cc966523.aspx .