Do you suffer from Index Anxiety? Do you lie awake, worrying about whether you are neglecting to defrag your indexes? We’ve all heard the chestnuts: rebuild all indexes that are over 30% fragmented; reorganize those that are between 10-30% fragmented; don't bother at all for small indexes. If you have the database equivalent of an eating disorder, worrying whether your indexes are too fat and fragmented, then I’d like to provide reassurance: they are often designed to be that way.

If Index rebuilding is the database equivalent of a body-image neurosis, then the SQL Server Maintenance Plan Wizard often delivers slimmer's snake-oil: if in doubt, rebuild! Even in cases where at least some effort is made to track index fragmentation, using DBCC SHOWCONTIG or the sys.dm_db_index_physical_stat DMV, I wonder whether the index-rebuild task is done more for reassurance than in confident expectation of a sustained performance boost.

The way it was explained to me is that, like people, indexes gravitate towards a certain natural weight. Some are naturally "skinny" and others naturally "fat". Let’s suppose that you have an index on a "name" column for a table storing members of your website. Entries will be regularly added to and deleted from the index column, space will be created and the index will grow fatter. However, this space will be reused quite effectively. As index entries are removed, the space will be reused as similar entries are added, and entries will naturally migrate from place to place in the index. If you regularly remove that space with a rebuild (or reorganization), all that will happen is that the engine will have to recreate it – working hard in the process – and the index will naturally grow fat again. It's the ultimate yo-yo diet, and it is all to little avail.

I'm not arguing that there aren't valid reasons to rebuild indexes – just betting that over ninety percent of index rebuilds are, at best, a waste of time, if not positively harmful. Too often, it seems to me, there is a missing task, which is to understand exactly how a table and its indexes are used by an application, to know the I/O that the most expensive queries perform, and to determine whether this work really will be reduced, for any significant period of time, by a rebuild. If you are constantly rebuilding indexes to maintain performance, then you need to look at your underlying schema design, and at the way your queries work, and see if there is a way to tune them to reduce range scans, and so on.

Crash Maintenance Plan slimming diets, followed by binge index growth, won't help to give your indexes the perfect figure. It is much better to give your indexes a healthy lifestyle.

As always, we'd love to hear what you think. The best contribution, made as a comment to the blog, will win a $50 Amazon voucher.

Cheers,

Tony.