Professional Microsoft® SQL Server® 2008 Administration

15.5. Index Maintenance

A number of problems 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, leading to additional results causing immediate page splits. Other pages can become very sparsely filled, causing many pages 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.

15.5.1. Monitoring Index Fragmentation

SQL Server 2000 used DBCC showcontig to monitor index fragmentation. With SQL Server 2005 and later editions, you 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 with our 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 use the following query:

SELECT index_id, index_level, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ...