Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

Comments

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

Leave a Comment

Please register or log in to leave a comment.