SQLUSA

Microsoft SQL Server 2000 Best Practices

How to reindex fragmented indexes in a database?

 

The following script will reindex all tables in the database with at least one index over 35% logical fragmentation:

 


CREATE TABLE #IndexFragmentation (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal)

INSERT #IndexFragmentation
EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES')


DELETE #IndexFragmentation where left(ObjectName,3)='sys'

DECLARE @Table sysname, @DynamicSQL varchar(512)
DECLARE @objectid int, @indexid int
DECLARE @Fragmentation decimal, @MaxFragmentation decimal
SET @MaxFragmentation = 35.0

DECLARE curIndexFrag CURSOR FOR
SELECT ObjectName, LogicalFrag = max(LogicalFrag)
FROM #IndexFragmentation
WHERE LogicalFrag >= @MaxFragmentation
AND indexid != 0 AND indexid != 255
GROUP BY ObjectName

OPEN curIndexFrag

FETCH NEXT
FROM curIndexFrag
INTO @Table, @Fragmentation

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DynamicSQL = 'DBCC DBREINDEX (' +RTRIM(@Table) + ', '''', 70)'
PRINT @DynamicSQL
EXEC (@DynamicSQL)

FETCH NEXT
FROM curIndexFrag
INTO @Table, @Fragmentation
END


CLOSE curIndexFrag
DEALLOCATE curIndexFrag

DROP TABLE #IndexFragmentation
GO

 

 

American Standard in SQL Server Training
 
SQLUSA.com Home Page