Database Performance

Written by Kimberly Clark
Bookmark and Share

In today's corporate world, a large number of big or mid-size companies rely on some sort of business management or enterprise software application to perform their day to day tasks. For the most part, the effectiveness of an application depends on the efficiency of the database that it is built on. Thus, in order to meet its daily objectives a business requires optimal database performance.

A large number of software applications are supported by what is known as relational databases, which are used to store data in distinct tables. This structure allows the user to perform various queries to retrieve, update, or cross reference the information contained in one or more table. The programming language used most often for modifying or obtaining information from a relational database is called Structured Query Language (SQL).

Since the database is widely considered the backbone of the software application, whenever there is any noticeable slowdown in the application's productivity the database is the first to be suspected. For this reason, a database administrator (DBA) requires a definitive method for gauging the true performance of databases and for identifying any constraints in the system. When the database is determined to be the culprit, there is generally one common factor causing the bottleneck and that is poorly coded SQL statements.

SQL Monitoring

To identify the SQL statements that are causing the biggest delays in the system, the DBA needs a systematic way to track each SQL statement that occurs in the application. By monitoring individual SQL statements, the DBA is able to find out exactly how long the statement takes to execute. This process also allows the DBA to measure just how much of the system's resources the SQL statement is consuming.

After the offending SQL statements are identified, the DBA can then attempt to develop a corrective action plan to eliminate the impediments. A couple of things worth considering are whether or not the query was coded properly or if it is still applicable to the function of the program. There might also be some user-generated queries that are affecting the system.

To keep an eye on database performance the DBA should perform regular checks or write a script that enables him or her to log the time different processes take to run. However, there are several products on the market that provide the DBA with the ability to automate the SQL monitoring process. Examples of these products include DBFlash from Configo Software, SmartDBA from BMC Software and Unicenter Database Performance Management Solution from Computer Associates.

Other Considerations

When fine tuning the performance of a database, other factors to consider, are the capabilities of the computer hardware. Hardware considerations include the available amount of memory and disk space as well as how fast the CPU and I/O (Input/Output) devices process or relay information. The database performance can also be affected by the operating system and other software loaded on the computer.

Once all the system resources are identified, the DBA can work on determining where the constraints occur. The investigation may reveal that more disk space or faster processing speed is required for better program execution. This information combined with the detailed analysis of the system's SQL statements allows the DBA to eliminate bottlenecks that prevent optimal database performance.

Bookmark and Share