Just like any other large, very active file, index files are likely to become fragmented over time. (They are also more likely to be corrupted, but that is another tip.) Because your database's performance is often (but not always) dependent upon the state of your indices, it is worthwhile to defragment them on a regular basis. Any workload that performs significant disk I/O will benefit the most from defragmentation, as is the case in systems that provide decision support. Transactional systems, as a rule, don't benefit as much from an index defrag. You will also find improvement in your updating statistics if you use the DBCC DBREINDEX command.
SQL Server provides four different commands to defrag indexes:
- DROP INDEX, then CREATE INDEX
- CREATE INDEX WITH DROP_EXISTING
- DBCC DBREINDEX
- DBCC INDEXDEFRAG.
When the DBCC SHOWCONTIG command shows that you have significant index fragmentation, as measured by the Logical Fragmentation and Page Density value, it is time to run one of these commands. Prior to running an index defrag it is important to check your system resources to make sure that you have adequate disk space available, and that you have a sufficient window during which to perform the operation.
The DBCC DBREINDEX command is rather similar in approach and effect to the CREATE INDEX command. DBCC DBREINDEX is run with the database offline, completely rebuilding the index(es) to their original page density targets. Use DBCC DBREINDEX if you need to have better access to statistics, and when you can run this command on fast servers as this command can make good use of SMP.
The DBCC INDEXDEFRAG command is used when you want to defrag a single index, and you need to do this while your SQL Server database is online and accessible to users. Each index that must be defragged requires a separate DBCC INDEXDEFRAG command. You'll also find that you can start and stop the DBCC INDEXDEFRAG command when needed, performing all or part of the defragmentation. When DBCC DBREINDEX is stopped prior to completion the system rolls back to its original state, as the command requires an atomic transaction.
A very thorough white paper on this topic may be found on Microsoft's TechNet site.
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.