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, resulting in 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.

Monitoring Index Fragmentation

In SQL Server 2000, we 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 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   (   DB_ID('People'),   OBJECT_ID('People'),   NULL,   NULL ,   'DETAILED'   ) go 

What you are looking for is any level of index where the avg_fragmentation_in_percent is higher than 0, although low single digits are also acceptable. In the case of the People table, I dropped and recreated the table, created the indexes, and then loaded 200,000 rows. After executing these steps the results don't look so good, and they show a high level of index fragmentation:

 index_id    index_level avg_fragmentation_in_percent ----------- ----------- ---------------------------- 1           0           98.6089375760737 1           1           96.4285714285714 1           2           0 6           0           1.38666666666667 6           1           57.1428571428571 6           2           0 7           0           98.3564458140729 7           1           100 7           2           0 8           0           99.1496598639456 8           1           98.4375 8           2           0 9           0           98.4857309260338 9           1           88.8888888888889 9           2           0 10          0           98.772504091653 10          1           40 10          2           0 

You would probably want to try to clean the indexes up a bit.

Cleaning Up Indexes

Now that you know you have fragmented indexes, you have three options for cleaning them up. The first and most comprehensive is to drop and recreate the indexes. This is the most intrusive, as each index needs to be dropped and then recreated. When an index is dropped, it is not available for use. Moreover, the drop and create operations are atomic, so the table is locked while this is happening, and not available for use. The second option is to use the statement ALTER INDEX REORGANIZE, which is new for SQL Server 2005. This statement replaces DBCC INDEXDEFRAG. The third option is to use the other new statement ALTER INDEX REBUILD., which replaces DBCC DBREINDEX.

If you have the luxury and time to take the table offline, you should use the first option, and drop and recreate the indexes. If you need to keep the table online, and want a less intrusive option, you should use either of the ALTER INDEX options. Both ALTER INDEX options are online, allowing the table to remain available during the index maintenance operation. The more online of the two is ALTER INDEX REORGANIZE, but it doesn't do as comprehensive a job with the index. We'll run each option on the badly fragmented index and see how well it does.

We'll start with ALTER INDEX REORGANIZE. After running the command:

 alter index all on people reorganize 

the index fragmentation now looks like this:

 index_id    index_level avg_fragmentation_in_percent ----------- ----------- ---------------------------- 1           0           0.960960960960961 1           1           96.4285714285714 1           2           0 6           0           1.38740661686233 6           1           57.1428571428571 6           2           0 7           0           2.53968253968254 7           1           100 7           2           0 8           0           1.9639407598197 8           1           98.4375 8           2           0 9           0           2.031144211239 9           1           88.8888888888889 9           2           0 10          0           2.45464247598719 10          1           40 10          2           0 

It made an improvement. You aren't back to the level you could be, but it was fast.

Now try the next option, ALTER INDEX REBUILD :

 alter index all on people rebuild 

After running this command, the indexes look like this:

 index_id    index_level avg_fragmentation_in_percent ----------- ----------- ---------------------------- 1           0           0 1           1           11.7647058823529 1           2           0 6           0           0 6           1           0 6           2           0 7           0           0 7           1           0 7           2           0 8           0           0 8           1           12.5 8           2           0 9           0           0 9           1           0 9           2           0 10          0           0 10          1           0 10          2           0 

It's pretty easy to see the large improvement in this over the results from REORGANIZE.

Finally, do the big one, and drop and recreate the indexes. After doing this, the indexes look like the following:

 index_id    index_level avg_fragmentation_in_percent ----------- ----------- ---------------------------- 1           0           0 1           1           0 1           2           0 6           0           0 6           1           0 6           2           0 7           0           0 7           1           0 7           2           0 8           0           0 8           1           12.5 8           2           0 9           0           0 9           1           0 9           2           0 10          0           0 10          1           0 10          2           0 

That's a quick look at index maintenance. What we haven't done is monitor the impact that the fragmentation might have had on queries, nor have we looked at many of the other options for index creation.