ONGOING MAINTENANCE AND GROWTH - Database Design and Development: An Essential Guide for IT Professionals - page 536

formance tuning: indexes, transactions and queries, and schema. Before that, let us
first state some obvious examples of tuning options at the four levels.
Hardware level. This is the lowest level at which tuning activities may be per-
formed. If the bottleneck is at the storage devices, some solution options are to add
disks or use improved configurations such as RAID technology. If main memory
buffer size is a restriction, try to add more memory. If the processor is a bottleneck,
upgrade to a faster processor.
Operating system level. Look into page sizes and block sizes. Consider the block
usage parameters and overhead to maintain overflow blocks. Review page faults
and page swapping.
DBMS level. Scrutinize and adjust buffer sizes, checkpoint intervals, and deadlock
resolution methods.
Applications level. Review the schema, tune indexes, and improve data access
schemes. At every possible opportunity, employ stored procedures for which
syntax checking, validation, optimization, and compilation have all been done
already.
Tuning Indexes
As you know, proper indexes speed up processing considerably. Indexes are the
most effective means to improve performance. After deployment, with users set-
tling down in their usage patterns, and with the availability of statistics, indexes may
be tuned successfully. Some queries may run long for lack of indexes; certain indexes
may not be utilized at all; a few indexes may cause too much overhead because the
attributes used for indexing are too volatile.
DBMSs provide trace facilities to sketch out the flow of operations in a query or
transaction. Use the trace on a query or a transaction to determine the indexes used.
Verify whether the indexes are appropriate. If not, revise the indexing.
The following is a list of general guidelines for tuning indexes:
• If queries are running slow, create appropriate indexes.
• If transaction updates are problems, reevaluate the indexes and ensure that
there are not too many indexes suggesting inefficient execution paths to the
optimizers.
• Make sure that proper indexing technique is defined for each index. For queries
with selections based on ranges of values, B-tree indexing is appropriate. Hash
indexing is best for selections based on exact values.
• Periodically reorganize and rebuild indexes to minimize overflow chains
and reclaim empty space. Without reorganization, indexes, especially B-tree
indexes, carry too much wasted space because of deletions.
• Optimizers depend on updated statistics about the database content such as
number of rows, number of distinct values for attributes, and so on. When there
is a choice of indexes for a table, the optimizer selects the right index based on