SQL Server : Identify Table Fragmentation

Tests the degree of fragmentation in your 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.

Versions

SQL Server 2005+

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

--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)