Professional SQL Server® 2005 Performance Tuning

11.4. Section 3: Index Maintenance

There are a number of problems that can occur with indexes over time as data changes in the underlying table. As rows are inserted, deleted, and updated, the distribution of data through the index can become unbalanced, with some pages becoming fully packed. This results in additional inserts causing immediate page splits. Other pages can become very sparsely packed, causing many pages to have to be read to access a few rows of data. These problems can be easily overcome with some simple index maintenance.

The first thing you need to do is implement some monitoring to figure out when the indexes are getting to the stage where they need attention. The second step is figuring out which of the various options for index maintenance you should use to clean up the index.

11.4.1. Monitoring Index Fragmentation

In SQL Server 2000, you used DBCC showcontig to monitor index fragmentation. With SQL Server 2005, you now have a new function, sys.dm_db_index_physical_stats. The syntax for this function is detailed in full in BOL, so here's a look at running it the People sample database:

use People
go

SELECT *
FROM sys.dm_db_index_physical_stats
  (
  DB_ID('People'),
  OBJECT_ID('People'),
  NULL,
  NULL ,
  'DETAILED'
  )
go

The results provide a lot of information, but there are just a few things you really want to focus on. In fact, to get the information you need on the level of fragmentation, you can just use look at these columns:

SELECT index_id, index_level, avg_fragmentation_in_percent ...