SQL Server Tuning – Find Fragmented Indexes

Problem Statement: Script to find fragmented Indexes.
Solution: No need to explain how killing fragmented indexes could be.Use following script to find the fragmented indexes and script to de-fragment them can be found here.

SELECT Db_name()              AS DBname,
Quotename(s.NAME)      AS CchemaName,
Quotename(o.NAME)      AS TableName,
i.NAME                 AS IndexName,
stats.Index_type_desc  AS IndexType,
stats.page_count       AS [PageCount],
stats.partition_number AS PartitionNumber,
CASE
WHEN i.fill_factor > 0 THEN i.fill_factor
ELSE 90
END                    AS [Fill Factor],
stats.avg_fragmentation_in_percent,
stats.fragment_count,
CASE
WHEN stats.index_level = 0 THEN ‘Leaf Level’
ELSE ‘Nonleaf Level’
END                    AS IndexLevel
FROM   sys.dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, ‘LIMITED’) AS
stats,
sys.objects AS o,
sys.schemas AS s,
sys.indexes AS i
WHERE  o.OBJECT_ID = stats.OBJECT_ID
AND s.schema_id = o.schema_id
AND i.OBJECT_ID = stats.OBJECT_ID
AND i.index_id = stats.index_id
AND stats.avg_fragmentation_in_percent >= 20
AND stats.page_count >= 1000
ORDER  BY stats.avg_fragmentation_in_percent DESC,
stats.page_count DESC