Managing Microsoft SQL Server Fragmentation

This topic is part of Configuring a Microsoft SQL Server Database for Siebel CRM.

Use the following Microsoft SQL Server command to determine whether a clustered index and its associated tables are highly fragmented:

DBCC SHOWCONTIG

If this command returns a value for scan density of less than 60%, then use the following Microsoft SQL Server command to defragment tables without having to drop indexes:

DBCC INDEXDEFRAG

You might want to use this option periodically against the entire database.

If DBCC SHOWCONTIG returns a value of less than 30%, or if you suspect that indexes might be interleaved on the disk, then consider rebuilding the index, by using the following command:

DBCC DBREINDEX

For more information about monitoring fragmentation, see Microsoft SQL Server documentation.