Count fragmented indexes

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 .

Author: DizzyBadger

SQL Server DBA, Cluster expert, Principal Analyst

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.