Systems Engineering and RDBMS

Getting table and Index names for fragmented indexes in SQL Server 2005

Posted by decipherinfosys on February 6, 2008

One of the readers had asked this question so we are posting the answer for that question.  Getting the table and Index names for fragmented indexes in SQL Server 2005 is pretty simple to do.  All we need to do is query the DMV and join it with sys.indexes.  Here is a simple script to demonstrate that:

SELECT
OBJECT_NAME(DMV.object_id)        AS TABLE_NAME
,SI.NAME                        AS INDEX_NAME
,avg_fragmentation_in_percent    AS FRAGMENT_PERCENT
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘Detailed’) as DMV
LEFT OUTER JOIN SYS.INDEXES AS SI
ON DMV.OBJECT_ID = SI.OBJECT_ID
AND DMV.INDEX_ID = SI.INDEX_ID
WHERE avg_fragmentation_in_percent > 10
AND index_type_desc IN(‘CLUSTERED INDEX’, ‘NONCLUSTERED INDEX’)
ORDER BY FRAGMENT_PERCENT DESC

Sorry, the comment form is closed at this time.

 
%d bloggers like this: