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 SQL Executive or the Database Maintenance Plan Wizard.

In the earlier discussion of tuning queries, we 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 is not essential to dump and reload tables to keep clustering properties intact, it can be useful to rebuild tables to reestablish fill factors and avoid page splits . The simple way that you can do this is by rebuilding the clustered index on the table using the DROP_EXISTING option that we discussed in Chapter 6. Using this option, the existing nonclustered indexes will not have to be re-created, since 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 contiguous a table is. 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.