Identify Table Fragmentation with DBCC Command

Tests the degree of fragmentation of indexes caused by page splitting using the DBCC SHOWCONTIG command. As DBCC SHOWCONTIG requires the ID of both the index table and index you can simply use this script which accepts the table and index names. DBCC SHOWCONTIG outputs several key measurements, the most important of which is the Scan Density. Scan Density should be as close to 100% as possible. A scan density of below 75% may necessitate a reindexing of all the tables in the database.

--Script to identify table fragmentation

--Declare variables

 

DECLARE

@ID int,

@IndexID int,

@IndexName varchar(128)

 

--Set the table and index to be examined

SELECT @IndexName = 'index_name' --enter name of index

SET @ID = OBJECT_ID('table_name') --enter name of table

 

--Get the Index Values

SELECT @IndexID = IndID

FROM sysindexes

WHERE id = @ID AND name = @IndexName

 

--Display the fragmentation

DBCC SHOWCONTIG (@id, @IndexID)