Subscribe to this blog
Briefcase
Print
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.
Posted by JPJ on 29 August 2011
Thanks for your post.
Using the code, I found my database to be too fragmented. I tried with Shrink command with no effect.
Is there any tools, can you recommend to fix the fragmentation?
Posted by vinay pugalia on 29 August 2011
Hello,
You can use the below SQL command to De-Fragment the Indexes -
DBCC INDEXDEFRAG
Please follow the below link -
msdn.microsoft.com/.../aa258286(v=sql.80).aspx