Data cache monitor

Job details

Name:	Data cache monitor
Platform:	Sybase
Category:	Performance
Description:	Collects data cache statistics based on the dbWatch tasks ‘System monitor collector’ which runs the ‘dbcc monitor’ utility continuously.
Long description:
Version:	1.3
Default schedule:	0,10,20,30,40,50 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version = ‘12′ & hasengine=‘YES’]

Parameters

Name	Default value	Description
history threshold	48	The amount of hours the statistics will be hold in the history table.

Job Summary

Purpose: The purpose of this job is to monitor and collect statistics on data cache performance and configuration for Sybase instances. This includes tracking the hit ratio and usage statistics of the data cache over time.
Why: This job is important for understanding the behavior of the data cache, which is crucial for the database performance. By monitoring data cache performance, database administrators can optimize and troubleshoot performance issues more effectively.
Manual checking: You can check this manually in the database by issuing the following SQL commands:

select t1.histr_date, sum((t1.value*1)/t1.elaps_time) calls, sum((t2.value*1)/t2.elaps_time) finds, 100.0 * sum(t2.value)/sum(t1.value) HR, t1.elaps_time from dbw_data_cache_histr t1, dbw_data_cache_histr t2 where t1.group_name = t2.group_name and t1.histr_date=t2.histr_date and t1.field_name='bufsearch_calls' and t2.field_name='bufsearch_finds'  and t1.elaps_time > 0 and t2.elaps_time > 0 group by t1.histr_date,  t1.elaps_time having sum(t1.value) > 0 order by 1 asc

Key Features and Reports

Data cache statistics are collected using the “dbcc monitor” utility continuously through the dbWatch task “System monitor collector.”
The report includes time-series data like hit ratio and call statistics, which are presented in both table and chart formats.

Tables and Charts Included in the Report

Data Cache Statistics Report Sections	Description
Data cache statistics	Shows historical data cache hit ratio
Data cache configuration	Details the configuration of each data cache buffer
Hit ratio for each buffer	Displays hit ratio segmented by individual cache buffers

Implementation Details

The job creates procedures and tables such as “dbw_data_cache_proc” and “dbw_data_cache_histr” to manage and store data.
It captures data cache statistics at preset intervals defined in the default schedule and retains data based on the history threshold specified.
The implementation includes safeguards such as cleanup on failure, where it drops created procedures and tables if the job fails to execute correctly.

Dependencies and Cleanup Procedures

The job is dependent on the presence of the “System monitor collector” task, which it leverages to obtain system statistics.
On job failure, the cleanup sequence involves dropping the “dbw_data_cache_proc” procedure and the “dbw_data_cache_histr” table to keep the system clean and ready for a fresh setup.

Version and Company

Version: 1.3
Company: dbwatch.no

By maintaining this monitoring setup, database administrators ensure optimal data cache utilization and performance, leading to overall enhanced performance of the database system.