Microsoft SQL Server has dbcc newalloc and dbcc checkdb commands to verify the integrity of tables and indexes within a database. Two procedures are necessary in addition to dbcc newalloc and dbcc checkdb to keep indexes at optimal condition with the database. These are update statistics and dbcc dbreindex.
To update statistics means to refresh the page of distribution statistics for an index that shows how the information is spread across the data pages of the index. This page of distribution statistics is used by the query optimizer to determine whether or not to use the index for the query. When this page becomes outdated because of inserts and deletes, the optimizer can send a query on a goose chase (aka. table scan). It is suggested that statistics be updated nightly on moderate sized OLTP systems and as needed for an OLAP or warehouse application. A highly summarized warehouse may have few if any inserts or deletes, so the index metadata is always current. Spend your maintenance time on integrity for such a database.
Periodically rebuilding the clustered indexes on a SQL Server database is useful to remove internal fragmentation and insure that the data pages allocated for the index are efficiently ordered for insert, update, select, and delete operations. Failure to periodically rebuild indexes will slow response times on the system if the data space is noticeably volatile. Track the changes in dbcc output files to fine tune the maintenance schedule. Find out which tables are growing and make certain those tables get the maintenance attention they need. Use dbcc showcontig to identify tables where internal fragmentation exists for a finer granularity of maintenance. One possible implementation would be to use the script_showcontig during off hours to collect the dbcc output, then review it for fragmentation. The most important information returned is the "Scan Density". The farther away from 100% this value gets, the more internal fragmentation exists in the table.
When tables row count is high the time required to rebuild indexes can be significant. To balance the benefits of index rebuild with the production system availability needs, it is suggested that index rebuilds be performed only when an adequate maintenance window of system time can be arraigned. If the server can be scheduled to be off line for several hours each night, why not update statistics nightly and rebuild indexes weekly, optimal performance can be maintained without interfering with production. But most of the time you just have to get as much maintenance done as possible within the narrow allotments of maintenance time. Businesses need to re-awaken to the obvious merit of well maintained systems. I recall a convincing argument that a system planned to be available 90% of the time is better than a planned 100% (24x7) availabe that is actually available only 99% of the time. Think about it! This is important!
It is possible, though very time consuming, to update statistics and/or rebuild indexes one at a time using the Manage Indexes dialogue within Enterprise Manager. More often, a script is prepared to update statistics and an index schema is prepared to drop and create the clustered indexes in the database. (Rebuilding the clustered index will force a rebuild any other indexes on a table.)
This provides the flexibility to exclude static data tables from unnecessary processing, thus reducing the time required to execute the update or rebuild and such scripts can be executed in batch. You could gain additional filtering to break the the tables into nightly sets to accomodate inadequate batch windows. The draw back is that the script must be maintained each time a clustered index is changed, added, or removed from the database. A robust way to programatically apply these static scripts and potentially automate script generation is via the admin subsystem.
A third alternative that does not add complexity to the maintenance processes is to dynamically identify and update statistics on or rebuild all indexes. This can be done using stored procedures that use the update statistics statement or the dbcc dbreindex utility inside of a cursor built from sysobjects. Updating statistics nightly and rebuilding indexes weekly is a good starting point in a database maintenance plan. Reindexing a table will update the statistics for the table, so running both in the same maintenance window is not recommended. Furthermore, rebuilding the clustered index will force all other indexes on a table to be rebuilt. My preferred method for implementing such dynamic scripts is well documented in the admin subsystem topic. If you are not able to implement the subsystem, there are a number of alternative approaches.
Working versions of a stored procedures to update statistics and rebuild all indexes in a database using dbcc dbreindex ---both within cursors built from sysobjects--- are available here. These are very simple and intended as starting points for more customized maintenance routines. For instance, you could build the cursor from a one column table of table names to contol which tables are maintained. Or you could check sysindexes to find the clustered index. Do what works best for your database.