Monday 22 August 2011

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.