About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

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.