This category reports the number of operations that required, or potentially required, maintenance to one or more indexes; that is, it reports the number of operations for which Adaptive Server had to at least check to determine whether it was necessary to update the index. The output also gives the number of indexes that were updated and the average number of indexes maintained per operation.
In tables with clustered indexes and one or more nonclustered indexes, all inserts, all deletes, some update operations, and any data page splits, require changes to the nonclustered indexes. High values for index maintenance indicate that you should assess the impact of maintaining indexes on your Adaptive Server performance. While indexes speed retrieval of data, maintaining indexes slows data modification. Maintenance requires additional processing, additional I/O, and additional locking of index pages.
Other sp_sysmon output that is relevant to assessing this category is:
Information on total updates, inserts and deletes, and information on the number and type of page splits
See “Transaction detail”, and “Page splits”.
Information on lock contention.
See “Lock detail”.
Information on address lock contention.
See “Address lock contention” and “Address locks”.
For example, you can compare the number of inserts that took place with the number of maintenance operations that resulted. If a relatively high number of maintenance operations, page splits, and retries occurred, consider the usefulness of indexes in your applications.
See Chapter 13, “Indexing for Performance,” in Performance and Tuning: Basics for more information.
