Finding Frangmentation of Indexes

Here is the query which will tell you How to find the fragmentation of a particular Index in Database


declare @database_id int
select @database_id = db_id()



SELECT
i.name AS [Index_Name],
CAST(i.index_id AS int) AS [Index_ID],
fi.index_depth AS [Depth],
fi.page_count AS [Pages],
fi.record_count AS [Rows],
fi.min_record_size_in_bytes AS [Minimu mRecordSize],
fi.max_record_size_in_bytes AS [MaximumRecordSize],
fi.avg_record_size_in_bytes AS [AverageRecordSize],
fi.forwarded_record_count AS [ForwardedRecords],
fi.avg_page_space_used_in_percent AS [AveragePageDensity],
fi.index_type_desc AS [IndexType],
fi.partition_number AS [PartitionNumber],
fi.ghost_record_count AS [GhostRows],
fi.version_ghost_record_count AS [VersionGhostRows],
fi.avg_fragmentation_in_percent AS [AverageFragmentation]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0
and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
INNER JOIN sys.dm_db_index_physical_stats
(@database_id, NULL, NULL, NULL, 'SAMPLED') AS fi
ON fi.object_id=CAST(i.object_id AS int)
AND fi.index_id=CAST(i.index_id AS int)
WHERE
(i.name='MyIndexName')
and((tbl.name='MyTableName' and
SCHEMA_NAME(tbl.schema_id)='dbo'))
ORDER BY
[Index_Name] ASC