Run this command as regular select statement from table, for example:

select * from sys.dm_db_index_physical_stats (DB_ID('Database_Name_Here'), object_id('Table_Name_Here'), NULL, NULL, 'SAMPLED')

The Avg_fragmentation_in_percent column returned by sys.dm_db_index_physical_stats is the logical and extent fragmentation of the index.

For more information about sys.dm_db_index_physical_stats, see Microsoft SQL Server 2005 Books Online at:

Handling Fragmentation

The table column Avg_fragmentation_in_percent returned by sys.dm_db_index_physical_stats reflects the degree of fragmentation per index. Based on this value, you can determine whether to handle the fragmentation and which method to use in handling it.

Use the following rough guidelines to determine the best method to correct
the fragmentation:

  • Between 5% and 30%. Use the ALTER INDEX REORGANIZE command to reorganize the index. Index reorganization is always executed online.
  • Greater than 30%. Use the ALTER INDEX REBUILD command to rebuild the index. Index rebuilding can be executed online or offline. Rebuild the indexes online to achieve the availability similar to that of the Reorganize option.

Very low levels of fragmentation (less than 5%) should not be addressed by either of these commands due to the negligible benefit gained from removing such a small amount of fragmentation.

For more information on defragmenting indexes, refer to "Reorganizing and Rebuilding Indexes" under Designing and Implementing Structured Storage in SQL Server Books Online (link).