Evaluate Index Use

Indexes usually improve OLTP transaction performance and query performance can also benefit from indexes. Although several indexes on a table might improve query performance, if more indexes must be updated, OLTP transactions and batch-update operations take longer. Consider the trade-off between query improvement and table-modification time for your application.

Analyze individual queries and transactions to decide what indexes to create.

The database server might use an index to improve query and transaction performance in the following ways:

• To make an index join possible instead of a table join
• To avoid sequential scans for low selectivity searches
• To avoid reading row data when the database server processes expressions that name only indexed columns
• To avoid a sort (including building a temporary table) when the database server executes the GROUP BY and ORDER BY clauses
• To use only the index to perform aggregate operations on indexed columns
• To replace repeated sequential scans of a table with nonsequential access

When you plan indexes, if possible avoid the following conditions:

• Indexing columns with many duplicate values or very large values
• Creating multiple indexes on tables that change often

For basic information about indexes, see Planning and Managing Indexes. For specific tuning information, see Create Appropriate Indexes for Queries and Create Appropriate Indexes for Transactions.