O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required