Last Updated: February 25, 2016
·
591
· purekrome

How to find out which MS Sql Indexes are fragmented

/* Find index fragmentation */
SELECT 
 DB_NAME(DATABASE_ID) AS [DatabaseName],
 OBJECT_NAME(OBJECT_ID) AS TableName,
 SI.NAME AS IndexName,
 INDEX_TYPE_DESC AS IndexType,
 AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,
 PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sysindexes SI 
ON DPS.OBJECT_ID = SI.ID AND DPS.INDEX_ID = SI.INDID
order by AvgPageFragmentation desc
GO