Database Scripts

 Log In    |   Sign Up

SQL Server : Identify Table Fragmentation

Added on Dec-11-2011 by admin
For SQL Server

Tags : performancetable 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.


SQL Server 2005+


--Script to identify table fragmentation 
--Declare variables 

@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 


Report Script

blog comments powered by Disqus