Developing a basic approach to performance measurement and tuning

To maintain optimum performance for your database applications, develop a plan for measuring system performance, making adjustments to maintain good performance and taking corrective measures when performance degrades. Regular, specific measurements can help you to anticipate and correct performance problems.

By recognizing problems early, you can prevent them from affecting users significantly. Early indications of a performance problem are often vague; users might report that the system seems sluggish. Users might complain that they cannot get all their work done, that transactions take too long to complete, that queries take too long to process, or that the application slows down at certain times during the day.

To determine the nature of the problem, you must measure the actual use of system resources and evaluate the results.

Users typically report performance problems in the following situations:

Response times for transactions or queries take longer than expected.
Transaction throughput is insufficient to complete the required workload.
Transaction throughput decreases.

An iterative approach to optimizing database server performance is recommended. If repeating the steps found in the following list does not produce the desired improvement, insufficient hardware resources or inefficient code in one or more client applications might be causing the problem.

To optimize performance:

Establish performance objectives.
Take regular measurements of resource utilization and database activity.
Identify symptoms of performance problems: disproportionate utilization of CPU, memory, or disks.
Tune the operating-system configuration.
Tune the database server configuration.
Optimize the chunk and dbspace configuration, including placement of logs, sort space, and space for temporary tables and sort files.
Optimize the table placement, extent sizing, and fragmentation.
Improve the indexes.
Optimize background I/O activities, including logging, checkpoints, and page cleaning.
Schedule backup and batch operations for off-peak hours.
Optimize the implementation of the database application.
Repeat steps 2 through 11.