How to find fragmentation information about an index on a table

To find fragmentation information about a table and its indexes, use the sys.dm_db_index_physical_stats dynamic management function which replaces the DBCC SHOWCONTIG statement available in previous versions of sql server.

 

SELECT      *

FROM  sys.dm_db_index_physical_stats

(

    { database_id | NULL }

    , { object_id | NULL }

    , { index_id | NULL | 0 }

    , { partition_number | NULL }

    , { mode | NULL | DEFAULT }

)

Where

database_id:      is the database_id of the database on the server. You can get it from sys.databases view. You can supply NULL if you want to use all databases.

Note: For current database, you can specify DB_ID(), which gets the current database you are working in.

Object_id:           The object id of the object for which you want to get the fragmentation information about. You can get this value from sys.objects. Or you can use OBJECT_ID() function to get the object id.

Index_id:             This is the ID of the index from the sys.indexes view if you want to get the info about indexes. If not, you can leave it blank.

Mode:   This is a string which can be LIMITED, DETAILED, or SAMPLED.

Example uses AdventureWorks database.

Use AdventureWorks

GO

-- First get the index id of the index

SELECT      index_id

FROM  sys.indexes

WHERE name = 'AK_Product_Name';

-- Say the index_id is 3.

-- Then get the index fragmentation

SELECT      *

FROM  sys.dm_db_index_physical_stats

(DB_ID(), object_id('Production.Product'),3, NULL, 'DETAILED')

DB_ID is current DB

table is Product