Index Maintenance

Question

What is Index Maintenance and how do I do it? How frequently do I have to do it? What are the benefits? This is related to a transaction table which is subject to frequently modifications; all DML operations will be run on that table.

Answer 1

In general, a DBMS will look after the index automatically so that it still works. However, with enough insert, delete and update operations, it is conceivable that there will be room for improvement in the index. That is, if the index were dropped and recreated, the resulting index would be smaller and more efficient than the modified index. And, ultimately, smaller indexes mean fewer I/O operations, and I/O operations are vastly more expensive than calculations.

There may be answers specific to SQL Server that I'm not aware of, but:

• Index maintenance would be a process that makes an index perform better.
• One way to do index maintenance is to drop and rebuild an index.
• Another way might be to alter the table in such a way that the indexes have to be rebuilt (cluster it, perhaps).
• You probably don't need to do it very often, but it will depend primarily on the quality of MS SQL Server and the way it handles indexes. I'd expect that to be at least adequate. Maybe once a month, if you have convenient down-time available to do it. Otherwise, less frequently to not at all.
• The benefit is less space used by the index and better performance using the index.

Answer 2

I second everything that Jonathan said - except for the frequency of index maintenance.

Well, if you happen to have a poorly designed index (such as a clustered index on a GUID key), you might actually need to do it at least every night - or even during the day, too.

As a general rule of thumb: if your index fragmentation is below 5%, all is fine. If you have fragmentation between 5% and approx. 30%, you should do an index reorganization:

ALTER INDEX (your index name) ON (your table name) REORGANIZE

If your index has index fragmentation of more than 30%, you need to rebuild it completely:

ALTER INDEX (your index name) ON (your table name) REBUILD

Rebuilding an index can be disruptive - try to do it at off-hours, e.g. during the night.

In order to determine index fragmentation, you can use this DMV query:

SELECT 
    t.NAME 'Table name',
    i.NAME 'Index name',
    ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.avg_fragment_size_in_pages,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count,
    ips.ghost_record_count,
    ips.Version_ghost_record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes,
    ips.forwarded_record_count
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN  
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count

Michelle Ufford has a great automatic index defrag script - highly recommended! Or then you should look into setting up SQL Server maintenance plans which can run e.g. every night and clean up your indices.

Marc