Oracle and IBM DB2 relational databases are generally installed behind the scenes to support the key functions of data storage and retrieval. Database performance can be considered an area of potentially high risk that requires corresponding robust controls.

The risks associated with database performance are related to business continuity and availability. Poor database performance can impact an organization’s reputation, service delivery and compliance with service level agreements.

As IT auditors, we have reviewed mission critical databases in some of the world’s largest processing environments and seen a full range of database environments in terms of management and control.

In the universe of risks and controls, preventive controls are preferable to detective or corrective controls. It is always better to prevent problems before they materialize into adverse conditions.

Performance monitoring is a key component of preventive controls that enables database administrators to proactively take action to ensure acceptable performance before problems appear.

The degrading of database performance can usually be identified early through effective monitoring and reporting. Corrective measures may include increasing the use of indexes, redesigning or re-normalizing data, partitioning tables, expanding buffer pools, adding CPU processing power or changing system configuration. Still another corrective measure is to collect different or more accurate statistics.

IBM’s approach to database performance monitoring is very methodical and is considered ‘best practice.’ First, objectives for database performance are established. Next, there is planning and implementation of specific monitoring. Performance reports must be analyzed on a regular schedule. Where performance is found to be unsatisfactory, database administrators must identify the constraints and tune the systems to balance resources.

There is a similar approach found in the COBIT framework with an IT ‘process’ to ‘monitor and evaluate IT performance.’ The specific control objective in the COBIT framework is ‘performance assessment’ which is to periodically review performance against targets, analyze the cause of any deviations and initiate remedial action to address the underlying causes.’

In setting performance objectives or targets, it is essential to define what exactly good performance is. These objectives should be realistic, understandable and measurable. Common database performance objectives include values for:

*Response time: acceptable response time for end users, accounting elapsed time (first SQL statement to thread termination), total transit time

*Throughput: average throughput (the total number of transactions or queries that complete within a given time)

*System availability: mean time to failure and the durations of down times.

These kinds of objectives are used to define requirements for resources such as processor speed, I/O and disk speed and capacity, amount of storage and additional hardware and software.

To achieve these objectives, actual performance monitoring and analysis can be implemented at four levels as follows:

*Continuous performance monitoring of statistics and deviations from past experience e.g. peak periods.

*Periodic performance monitoring of ‘snapshots’ during peak loads and during normal conditions. Trends can be identified.

*Detailed performance monitoring to research specific issues that have been identified.

*Exception performance monitoring to identify exceptional values or events e.g. high response times or deadlocks.

To sum up, database performance monitoring is very important in managing risks associated with performance degradation. IBM’s framework can be used to develop a performance monitoring program. Performance objectives should include database response time, throughput and system availability. Degraded performance that has been identified can be corrected through targeted changes to database objects, system hardware and software configurations.

References:
IBM DB2 Universal Database for z/OS Administration Guide. www.ibm.com

ISACA. Control Objectives for IT (COBIT) Framework 4.1. www.isaca.org

Want to find out more about database best practices, then visit Sarah Abelow’s site on how to find the best Database Auditors for your needs.