Cleaning Up SQL Server Indexes

Index cleanup should always be part of all database maintenance operations. You need to perform these index cleanup tasks on a regular basis, depending on how fragmented indexes become due to changes to the data. If your indexes become highly fragmented, you can defragment it through one of the operations described here:

• Reorganize an index:
  • Reorders and compacts leaf level pages
  • Performs index reordering online, no long-term locks
  • Good for indexes with low fragmentation percentages
• Rebuild an index:
  • Drops and re-creates the index
  • Reclaims disk space
  • Reorders and compacts rows in contiguous pages
  • Online index rebuilt option available in Enterprise Edition
  • Better for highly fragmented indexes

Table 14-2 lists the general syntax for index operations for the DimCustomer table.

Table 14-2: Index /operations Syntax for DimCustomer Table

Operation	Syntax
Create Index	CREATE INDEX IX_CustomerAlternateKey ONDimCustomer_CustomerAlternateKey
Reorganize Index	ALTER INDEX IX_DimCustomer_CustomerAlternateKey ON DimCustomer REORGANIZE
Rebuild Index	ALTER INDEX IX_DimCustomer_CustomerAlternateKey ON DimCustomer REBUILD
Drop Index	DROP INDEX IX_DimCustomer_CustomerAlternateKey FROM DIMCustomer

Indexes may become heavily fragmented over time. Deciding whether to reorganize or rebuild indexes depends in part on their level of fragmentation and your maintenance window. Generally accepted fragmentation thresholds to perform an index rebuild range between 20 percent and 30 percent. If your index fragmentation level is below this threshold, performing a reorganize index operation may be good enough.

But, why not just rebuild indexes every time? You can, if your maintenance window enables you to do so. Keep in mind that index rebuild operations take longer to complete, time during which locks are placed and all inserts, updates, and deletions have to wait. If you are running SQL Server 2012 Enterprise Edition, you can take advantage of online index rebuild operations. Unlike standard rebuild operations, online index operations allow for inserts, updates, and deletions during the time the index is being rebuilt.