Monitor actual system usage

The optimal set of indexes is strongly dependent on the actual usage of the system; that is, how frequently data is:

• Added

• Changed

• Read

• Deleted or moved to offline storage (and thus in conjunction with the how frequently data is added, how big various tables can become)

So actual statistics about the usage of the database should drive the choice of indexes.

Be careful to collect statistics during a wide enough period of time so that the results are representative of the way the system operates as a whole. Traffic at a shift change, for example, might be very different from that in the middle of a shift. Also, be sure to catch the generation of any large sets of periodically-generated reports.

Monitoring system activity should be an ongoing database administration task. As the system grows and its usage changes over time, new statistics should be obtained and indexes should be re-examined.