Introduction To Database Performance And Tuning

Database performance and tuning is referring to the performance of database applications (such as SQL Server, Oracle, My SQL and other databases). Database performance monitoring software typically tracks a variety of data about size, response times, and number of connections over time.

Database performance and tuning is difficult in any modern relational database management system. Database performance is not a simple task because it depends on your requirements, the operating system and the target hardware. But when you design a database, there are some topics to keep in mind for increasing database performance.

• Memory Utilization-If you have tuned the instance and still need to improve performance, verify your work or try to reduce system time.
• Wait Stats Analysis-Perform advanced resource wait statistics analysis to proactively reduce performance bottleneck.
• Fragmentation-Identify the optimal settings of your database files and tables to reduce fragmentation and reduce them.
• Backup and Recovery-you may also need to tune performance of the database during instance recovery.
• Deadlock Detection and Resolutions-Multiple processes persistently blocking each other, in an irresolvable state, will eventually result in a halt to processing inside the database engine.
• Index analysis-Query performance as well as speed improvement of a database can be done using Indexes.

Configuring a Database for Performance

This is an overview of the Oracle methodology for configuring a database for performance. Although performance modifications can be made to the Oracle database instance at a later time, much can be gained by proper initial configuration of the database for the intended needs.

This contains the following sections

• Performance Considerations for Initial Instance Configuration.
• Creating and Maintaining Tables for Good Performance.
• Performance Considerations for Shared Servers.

Database tuning describes a group of activities used to optimize and homogenize the performance of a database. It usually overlaps with query tuning, but refers to design of the database files, selection of the database management system (DBMS) application, and configuration of the database's environment (operating system, CPU etc.).

SQL tuning involves three basic steps

• Identifying high load or top SQL statements that are responsible for a large share of the application workload and system resources, by reviewing past SQL execution history available in the system.
• Verifying that the execution plans produced by the query optimizer for these statements perform reasonably.
• Implementing corrective actions to generate better execution plans for poorly performing SQL statements.

These three steps are repeated until the system performance reaches a satisfactory level or no more statements can be tuned.