Data cache statistics


Job details

Name: Data cache statistics
Platform: Sybase
Category: Performance
Description: Collects data cache statistics.
Long description:
Version: 1.1
Default schedule: 6,16,26,36,46,56 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version > ‘12′ & hasengine=‘YES’]

Parameters

Name Default value Description
keep data for 7 The number of days to keep statistics.

Job Summary

SELECT * FROM master..monDataCache;

Report Template Details

Date Logical reads per sec. Physical reads per sec. Physical writes per sec.

Task Implementation and Dependencies

create table dbw_data_cache_tab(logical_reads bigint NULL, physical_reads bigint NULL, physical_writes bigint NULL, histr_date datetime NULL)
create table dbw_data_cache_histr_tab(logical_reads bigint NULL, physical_reads bigint NULL, physical_writes bigint NULL, histr_date datetime NULL, logical_reads_dif bigint NULL, physical_reads_dif bigint NULL, physical_writes_dif bigint NULL, time_interval_sec bigint NULL)

** Stored procedure to gather and update task values:

create procedure dbw_data_cache_stat_proc @taskID INT ...
drop procedure dbw_data_cache_stat_proc
drop table dbw_data_cache_histr_tab

Installation Details

By monitoring parameters like logical reads, physical reads, and writes per second, the DBA can understand the load and performance of the cache system. Adjustments can then be made either by reconfiguring the system or by optimizing queries to reduce load. Subsequent reports generated provide insight into the effectiveness of changes made and whether any further tuning is needed.