Engine CPU monitor

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor the CPU usage by capturing statistics related to the CPU and I/O operations of database engines.
• Why: Monitoring CPU and I/O utilization is crucial for assessing the performance of database servers. High usage could indicate an under-provisioned server or inefficient queries, which might lead to slower response times and potentially affect overall application performance.
• Manual Checking: You can check CPU and I/O usage manually in the database with the following SQL commands:

select engine_name + ' (CPU busy)' engine, cpu_busy value, histr_date from dbw_cpu_histr
union
select engine_name + ' (I/O busy)', io_busy value, histr_date from dbw_cpu_histr
union
select engine_name + ' (Idle)', 100.0 - cpu_busy - io_busy value, histr_date from dbw_cpu_histr
order by histr_date asc

Detailed Job Description

This monitoring job targets instances of type Sybase with major version 12 that contain an engine. The job involves the ‘dbw_engine_cpu_proc’ object which is dependent on the continuous running of the “dbcc monitor” utility by the task “System monitor collector.”

This process not only collects but also analyzes data about the amount of time the CPU is busy and the amount of time spent on I/O operations. This information is stored in historical tables and is used for computing usage statistics over specified intervals.

Dependencies

• This procedure is self-dependent and requires existing history tracking objects.
• Relies on ‘dbw_engine_cpu_proc’, ‘dbw_engine_cpu_histr’, and ‘dbw_cpu_histr’ for operation.
• A associated utility, the ‘dbw_sysmon_collector’, is crucial as it provides raw input statistics by continuously executing the “dbcc monitor” utility.

Implementation Details

• Tables for capturing and retaining CPU statistics are created and named ‘dbw_engine_cpu_histr’ and ‘dbw_cpu_histr’.
• The main implemented procedure ‘dbw_engine_cpu_proc’ executes tasks to collect and maintain CPU and I/O statistics, calculate averages, and maintain a history controlled by the ‘history threshold’ parameter which dictates the duration for which data should be retained.

Automated Cleanup

• On failure, it is set to automatically drop the procedures and tables involved in the computation of CPU and I/O statistics.

Reporting

• Reports are generated periodically showcasing:
  • Kernel configurations affecting CPU utilization.
  • Historical CPU busy and I/O busy statistics alongside idle percentages over time.
• These reports help in understanding the performance trends and identifying potential bottlenecks.

In conclusion, this monitoring job plays a crucial role in active database performance management by helping administrators understand and optimize CPU and I/O usage within their Sybase instances.