Index Fragmentation SQL Server 2000
Find Index Fragmentation Statistics by DBCC SHOW CONTIG
USE [DATABASE_NAME]
DBCC SHOWCONTIG('TABLE_NAME')
--or--
USE [DATABASE_NAME]DBCC SHOWCONTIG('TABLE_NAME') WITH ALL_LEVELS, TABLERESULTS, NO_INFOMSGS
#Example on SAP table RESB
use DEV
go
dbcc showcontig ('dev.RESB')
Logical Fragmentation < 10% Do Nothing
10% < Logical Fragmentation <=30% (Reorganize by “DBCC INDEXDEFRAG”)
Logical Fragmentation >30% (Rebuild by “DBCC DBREINDEX”)
Reorganizing an Index [Online operation smaller granularity ]
DBCC INDEXDEFRAG ([DATABASE_NAME], [TABLE_NAME], [INDEX_NAME])
GO
Rebuilding an Index [Online operation Most index types can be rebuilt online ]
USE [DATABASE_NAME];
GO
ALTER INDEX [INDEX_NAME] ON [SCHEMA.TABLE]
REBUILD WITH (FILLFACTOR=[FILL_FACTOR_VALUE_BETWEEN_0_100], ONLINE=[ON|OFF]);
GO
-- Rebuild all indexes in a table with using parameters --
