Here is a query that you can use to find out in sql 2005 which indexes are fragmented enough to the point that they need defragmenting or rebuilding. In this query I make use of the sys.dm_db_index_physical_stats function and the sys.indexes system view. It is recommended by Microsoft that for indexes which are fragmented up to but not over 30% a simple defragmentation using the ALTER INDEX statement should be sufficient, however if the index is more than 30% fragmented then it is probably worth rebuilding the entire index.
select object_name(indexView.object_id) as TableName,indexView.name AS IndexName,
indexFunction.avg_fragmentation_in_percent as FragmentationInPercent
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED') indexFunction
JOIN sys.indexes indexView ON
indexView.object_id = indexFunction.object_id
AND indexView.index_id = indexFunction.index_id
where indexFunction.avg_fragmentation_in_percent > 10
The sys.dm_db_index_physical_stats function takes several parameters. More information on the various parameters can be found at this msdn link http://msdn2.microsoft.com/en-us/library/ms188917.aspx . I will however, make a fuss on one of the parameters I have used and that is the last one which is the 'mode' parameter, this indicates that type of sampling you want employed. I chose detailed even if it took longer because if I was in the process of trying to optimise indexes on a sql 2005 database I would want to know all of the fragmented ones, and the detailed parameter achieves this by scanning all pages and returning all statistics. Depending on your application, however, you may simply choose to view those [indexes] with greater than 30% fragmentation.
Farayi (a.k.a RAMROD http://en.wikipedia.org/wiki/Image:Star_Sheriffs_Ramrod.jpg )
