T-SQL to find Fragmented Indexes

Fragmentation of Indexes is one of the reason for low performing queries resulting in a poor application performance.

Today, I will present a simple script which will help in identifying the level of fragmentation in a Database.

--Replace this with the name of the Database for which we want to find the fragmentation.

USE <DBName> 

GO

DECLARE @DBName AS VARCHAR(10) = 'DBName'

DECLARE @DBID AS INT = DB_ID(@DBName)

DECLARE @AllowedFragmentation AS INT = 70 --A acceptable value in Percent(%) for fragmentation.

DECLARE @Qry AS VARCHAR(MAX)

SELECT

  --@DBID [DBID],

  --@DBName DBName,

  PS.OBJECT_ID ObjectID,

  COALESCE(T.name,V.name) ObjectName,

  PS.index_id,

  I.name IndexName,

  PS.page_count AS TotalPages,

  (PS.page_count * 8)/1024.0 as TotalMB,

  ((PS.page_count * 8)/1024.0) * (PS.avg_fragmentation_in_percent/100) as ReclaimableMB,

  PS.avg_fragmentation_in_percent AvgFragmentationPercent

FROM

  sys.dm_db_index_physical_stats (@DBID, NULL, NULL, NULL, NULL) AS PS

INNER JOIN sys.indexes AS I

  ON PS.OBJECT_ID = I.OBJECT_ID

    AND PS.index_id = I.index_id  

LEFT JOIN sys.tables T

  ON T.object_id = I.object_id

LEFT JOIN sys.views V

  ON V.object_id = I.object_id

WHERE

  PS.database_id = @DBID

  AND PS.avg_fragmentation_in_percent > @AllowedFragmentation 

ORDER BY    

  PS.avg_fragmentation_in_percent DESC 

Here, I have considered 70% fragmentation as an acceptable level of fragmentation.

Hope, this helps.