Disk activity monitor

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and report on disk activity statistics for Sybase instances, specifically targeting environment setups with major version 12 and engines present.
• Why: This job is important as disk activity is a critical component of database performance. Monitoring reads, writes, and various asynchronous I/O (AIO) delays can help in early detection of potential performance bottlenecks or issues. Effective monitoring can lead to proactive management, preventing performance degradation and system downtime.
• Manual checking: To manually check disk activity statistics in the database, you can use SQL commands related to the specific tables and procedures created by this job. Here is an example command to view total reads and writes:

SELECT group_name, SUM(value) AS total_reads_and_writes
FROM dbw_disk_activity_histr
WHERE field_name IN ('total_reads', 'total_writes')
GROUP BY group_name;

Job Details

• Name: Disk activity monitor
• Object: dbw_disk_activity
• Description: Collects statistics created by the “System monitor collector” task.
• Version: 1.3
• Company: dbwatch.no
• Category: Performance

Dependencies and Pre-requisites

The disk activity monitor has several key dependencies:

• Requires the “System monitor collector” task to be installed and running, as it utilizes the ‘dbcc monitor’ utility to collect necessary statistics.
• Self-referential dependency to its own procedure and table resources to ensure data consistency and integrity.
• The job processes data stored in the ‘dbw_disk_activity_histr’ table, which is essential for history management based on the configured history threshold.

Cleanup and Failure Management

The job includes mechanisms to handle failures and maintain the environment clean:

• On failure, specific procedures and tables such as ‘dbw_sysmon_check’, ‘dbw_sysmon_usage’, and ‘dbw_sysmon_usage_histr’ are dropped to prevent corrupt data from affecting subsequent operations.

Implementation Details

This job involves several SQL operations as part of its implementation:

• Creating necessary tables and inserting initial dummy data.
• Procedures are set up to manage and maintain the disk activity history based on administration-defined thresholds.
• Data is periodically updated by inserting new statistics available from the system monitor collector and cleaning up outdated records as defined by the ‘history threshold’.

Reporting and Visualization

The job generates reports that provide insights into the disk activity, highlighting:

• Total reads and writes for all monitored devices.
• Historical data on disk reads and writes, segmented as per group definition.
• Delays related to various asynchronous I/O operations, which can help in identifying potential issues in handling I/O operations effectively.
• The reports are visualized using several charts that categorize and series the data effectively to present understandable and actionable insights.

Tables and scripts used for generating these visualizations include sophisticated SQL queries and utilize the custom ‘dbw_disk_activity_histr’ table tailored specifically for this monitoring job.