Procedure cache check

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and check the status of the procedure cache size in a Sybase database instance, particularly focusing on the ratio of used cache relative to total cache size.
• Why: This job is important because maintaining an optimal procedure cache size is crucial for efficient database performance. Excessive usage can lead to slower query processing and potential cache overflows, which could degrade system performance and affect application functionality.
• Manual checking: To manually check the procedure cache status in the database, you can execute the following SQL command:

exec sp_monitorconfig 'procedure cache size'

Job Details

System and Version Requirements

• The task runs specifically for Sybase database instances with major version 12 that have the engine check parameter set to ‘YES’.

Frequency

• The procedure cache checks are scheduled to occur every 10 minutes.

Functionality

• The task identifies the total cache space and the amount used at various intervals.
• Generates warnings if the cache usage exceeds predefined thresholds, 90% for a warning and 98% for an alarm, which help in proactive issue resolution and prevent performance degradation.
• The measurements are logged into a historical table, allowing trends and patterns over time to be analyzed.

Implementation Details
The job includes:

• Creating a temporary table to capture the procedure cache size.
• Executing the stored procedure ‘sp_monitorconfig’ to populate the cache size data.
• Calculation of total, free, and used cache space.
• Inserting the historical record of cache status.
• Maintenance of the historical data by purging entries older than a specified number of days.
• Removal of temporary structures once data collection is complete.

Dependencies

1. In addition to its own recurring execution, this task has two main dependencies:
2. self-dependency on the dbw_procedure_cache_check.
3. dependency on the historical table ‘proc_cache_check_histr’ for logging cache events.

Cleanup and Failure Strategy

• .Should the job fail, it is set up to drop the ‘dbw_procedure_cache_check’ stored procedure and the ‘proc_cache_check_histr’ historical data table to ensure there are no residuals that could affect retry mechanisms or cause data inconsistencies.

Reporting

• The job provides a specific report which visualizes the procedure cache usage over time, segmented by ‘used’ and ‘free’ metrics, offering insights into the cache dynamics.
• The reporting tool presents this data in a category chart format, where history dates form the categories and aggregation types form the series for a clear, visual representation.

This monitoring job is well-constructed to ensure that the system’s procedure cache operates within optimal parameters, maintaining both performance stability and efficiency in database operations.