Databases Reference
In-Depth Information
Benefit of the index
= I/O time (all queries without index) -
I/O time (all queries with index).
Cost of the index
= I/O time (all updates with index) -
I/O time (all updates without index).
Note that each update has two components: the query to access the row(s) to be
updated and the actual update itself. Clustered indexes tend to have higher update
costs than nonclustered indexes and should be looked at carefully.
Basic rule:
Create the index if the benefit is greater than the cost. This rule has
exceptions that must be considered, since it is based only on I/O time. The prior-
ity of queries may be much higher than the priority of updates, especially if
updates can be batch processed at noncritical hours in the day, which assumes
that you can live with a few hours' delay in updates. In this case, the benefit may
actually be less than the cost, as measured by I/O time, since priority may be a
more important factor.
On the other hand, if updates must immediately be posted, but queries can be
delayed, then the balance of priority shifts to the updates and the benefit must be
significantly greater than the cost to justify the use of the index.
Design Decision 7: How do I know I made the right indexing choice?
After one
has made a decision about choice of index, it is important to investigate whether
the right choice has been made or not. The original decision itself should have been
done using some analytical performance tradeoff analysis that estimates the I/O
time needed to answer a set of known queries on that table or set of tables and
index. The design decision is very often made on this well-established, but theoret-
ical basis.
Once the new index is set up, new queries are often established to take advantage of
it, and data needs to be collected both before the index is implemented and after-
ward to determine whether or not the index is making the database perform better.
Data collection facilities are now common to the major vendors. IBM uses the
DB2 Instrumentation Facility to collect performance trace data for analysis;
Microsoft uses data collection and analysis tools for SQL Server 2005, such as Per-
formance Monitor and PSSDIAG; and Oracle uses the Automatic Workload
Repository (AWR) in the Common Manageability Infrastructure to collect, main-
tain, and provide access to key statistics for performance self-tuning. If there is no
improvement in performance, or if there is a degradation in performance, then the
search needs to continue for a better solution. Automated tools are most useful here
(see Chapter 11).
