In order for your system to perform as it should,
regular maintenance must be performed.
A large portion of this can be automated.
However, keep a close eye on the systems to make sure that users are not negatively affected.
Regular maintenance tasks include the following:
* Index Rebuilds
Much improved in SQL Server 2005,
as indexes can be built without locking tables,
although performance will drop as the index is rebuilt
* Compressing data files
Frees up some disk space on your system,
especially if you run frequent insert/delete statements
* Updating Statistics
Makes sure that SQL Server is using the up-to-date statistics
to create its execution plans,
ensuring high query performance
* Database Consistency
Checks Check for corruption within databases/tables/indexes
These items can be run within Maintenance Plans
and can be set up with the assistance of a wizard.
You might want to run other maintenance routines such as data purges,
which can be set up manually.
Source:
Microsoft SQL 2005 Administrator's Companion, Page 53-54