:::: MENU ::::

Rebuilding Indexes in SQL Server 2005

  • Nov 05 / 2008
  • 0
dbDigger, Indexes, Performance Tunning and Optimization

Rebuilding Indexes in SQL Server 2005

When data is added to or updated in the index, page splits occur. These page splits cause the physical structure of the index to become fragmented. In order to restore the structure of the index to an efficient state, the index needs to be rebuilt. The more fragmented the index, the more performance improvement will result from rebuilding the index.

With SQL Server 2005 you can view the fragmentation in an index via the sys.dm_db_index_physical_stats function.

If the percentage of index fragmentation is less than or equal to 30 percent, Microsoft recommends correcting this with the ALTER INDEX REORGANIZE statement. If the percentage of fragmentation is greater than 30 percent, Microsoft recommends using the ALTER INDEX REBUILD WITH (ONLINE = ON) statement.

With SQL Server 2005, there are additional methods of rebuilding and reorganizing indexes that have been added

  • ALTER INDEX REORGANIZE
  • ALTER INDEX REBUILD
  • CREATE INDEX WITH
  • DROP_EXISTING=ON
  • DBCC INDEX DEFRAG
  • DBCC INDEX REBUILD
  • ALTER INDEX REORGANIZE
  • ALTER INDEX REBUILD
Consult us to explore the Databases. Contact us