System Maintenance

SQL Server requires much less regular system maintenance than most comparable products. For example, because pages are automatically split, it maintains data clustering characteristics; most products that support data clustering make you do data reorganization to keep the data clustered. However, you should perform a couple of regular maintenance tasks for performance reasons. You can easily schedule these tasks (such as updating statistics and rebuilding your clustered index) using the SQL Server Agent service or the Database Maintenance Plan Wizard.

In the earlier discussion of tuning queries, I explained the importance of index distribution statistics. If you have not enabled automatic updating of statistics, you should update statistics frequently so that any significant changes in the volumes or distribution of data are reflected. Although it's not essential to dump and reload tables to keep clustering properties intact, doing so can be useful in rebuilding tables to reestablish fill factors and avoid page splits. The simple way to do this is to rebuild the clustered index on the table using the DROP_EXISTING option. When you use this option, the existing nonclustered indexes do not have to be re-created because the clustering key will stay the same. You can also use the DBCC DBREINDEX command to rebuild all the indexes on a table. Rebuilding the clustered index helps keep a table from becoming fragmented and makes read ahead more effective. You can use DBCC SHOWCONTIG to determine how much fragmentation a table has. To remove fragmentation without incurring the overhead of completely rebuilding indexes, you can use DBCC INDEXDEFRAG. Of course, you should also regularly do other tasks, such as performing backups and periodically checking the structural integrity of the database (for example, by using DBCC CHECKDB), but these are not performance-related tasks.