Procedure cache check

Job details

Name:	Procedure cache check
Platform:	Sybase
Category:	Performance
Description:	Checks the procedure cache.
Long description:
Version:	1.1
Default schedule:	1,21,41 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version > ‘12′ & hasengine=‘YES’]

Parameters

Name	Default value	Description
warning threshold	95	The check will give a warning when the cache usage is above this ratio (in %).
alarm threshold	98	The check will give an alarm when the cache usage is above this ratio (in %).
keep data for	7	The number of days to keep records.

Job Summary

Purpose: The purpose of this job is to monitor the procedure cache size and usage in Sybase ASE instances with major version greater than 12 that have procedure cache enabled. The job assesses cache utilization to preempt performance degradation due to high cache usage.
Why: This job is important because managing procedure cache effectively ensures that SQL queries are processed faster by reducing the number of physical reads required for frequently executed procedures. Over-utilization of the procedure cache can lead to significant performance bottlenecks.
Manual checking: You can check the procedure cache usage manually using the following SQL commands:

exec sp_monitorconfig 'procedure cache size'

Details of Implementation

The job involves creating a procedure ‘dbw_procedure_cache_check’ and a table ‘proc_cache_check_histr’ to store historical data about the cache usage.
The procedure calculation involves fetching configuration values for the cache, calculating the space used, free space, and the ratio of used to total space. Depending on predefined thresholds, the job can raise different levels of alerts (warning or alarm).
If the cache usage ratio exceeds the ‘alarm threshold,’ an alarm status is set with detailed information about the space used. If it only exceeds the ‘warning threshold,’ a warning status is issued.

Dependency and Cleanup

This task depends on itself (‘dbw_procedure_cache_check’) and an historical data table (‘proc_cache_check_histr’).
On failure, the procedure and the historical table are dropped to prevent any inconsistent data leftovers.

Data Retention

Historical data about the procedure cache usage is retained based on the ‘keep data for’ parameter, typically for a number of days specified in the job configuration. This helps in tracking and analyzing trends over a period.

Scheduled Execution

The system is set to execute the job every 20 minutes by default, ensuring frequent checks and up-to-date status of the procedure cache.

Reporting

The provided dbWatch report template includes a chart reflecting historical statistics of the procedure cache size, categorized by free and used space measurements over time.
Additional textual content in the report explains the importance of maintaining an optimal cache hit ratio and provides SQL commands to adjust the procedure cache size if necessary.

This detailed approach helps maintain system performance by proactive monitoring and management of crucial database configuration parameters, enhancing overall database health and response times.