Evaluating Index Costs

Although indexes are important for OLTP applications and can speed query execution, before you create an index consider the disk-space and update-time costs described in the following sections.

Disk-Space Costs

One cost of an index is disk space. An index can require large amounts of disk space. For some indexes, you might have as many index pages as table data pages. If you choose an attached index, consider the potential for extent interleaving, as described in Reducing Extent Interleaving.

For information about estimating index size, see Appendix A. Estimating Table and Index Size.

Update-Time and I/O Costs

For performance tuning, a more important cost of an index is the time and I/O required to update the index whenever the table is modified. If you index tables that are modified at specific times by batch load jobs, such as tables in databases used exclusively for DSS queries, the time cost of updating the index is incurred once, at load time. However, for OLTP applications that update tables continually, the time cost of updating an index might be significant.

The descriptions of update-time cost make the two following assumptions:

• Approximately two pages must be read to locate an index entry, as is the case when the index consists of a root page, one level of branch pages, and a set of leaf pages. The root page is assumed to be in a buffer already. The index for a very large table has at least two intermediate levels, so about three pages are read when you reference such an index.
• One index is used to locate a row that is being altered. The pages for that index might be found in page buffers in shared memory. However, the pages for any other indexes that need altering must be read from disk. Presumably, one index is used to locate a row being altered.

With these assumptions, conventional B-tree index maintenance adds different amounts of time for different kinds of table modifications, as the following list describes:

• Delete a row

  All entries for a deleted row must be deleted from all indexes.

  The database server must look up the entry for the deleted row (two or three pages read in), and rewrite the leaf page. The write operation to update the index is performed in memory, and the leaf page is flushed when the least-recently-used (LRU) buffer that contains the modified page is cleaned. So this operation requires two or three page accesses to read the index pages if needed, and one deferred page access to write the modified page.

• Insert a row

  An inserted row requires an insert into all indexes.

  The database server must locate a place for the inserted row (two or three pages read in) and rewrite the page (one deferred page out), for a total of three or four immediate page accesses per index.

• Update a column

  If the updated column is indexed, each affected row must be looked up in the index (two or three pages in). The leaf page must be rewritten to change the old entry (one deferred page out), the new column value must be located in the same index (two or three more pages in), and the row must be entered (one more deferred page out).

Insertions and deletions change the number of entries on a leaf page. Almost every action that changes the number of entries on an index page requires some additional work to deal with a leaf page that has either filled up or been emptied. However, if the number of such actions is greater than 100, this additional work occurs less than 1 percent of the time. You can often ignore this work when you estimate the I/O impact. For more information about index structure and maintenance costs, seeEstimating Conventional Index Page Size.

In short, when a row is inserted or deleted at random, three to four added page I/O operations occur for each index. When a row is updated, allow six to eight page I/O operations for each index that applies to an altered column.

If a transaction is rolled back, all this work must be undone. For this reason, rolling back a transaction on an indexed table can take a long time.

Because the alteration of the table row itself requires only two page I/O operations, index maintenance can be the most time and I/O intensive part of data modification. For information about one way to reduce this cost, see Dropping Indexes.