Temp cache statistics


Job details

Name: Temp cache statistics
Platform: Sybase
Category: Performance
Description: Collects temp cache statistics from master..monTempdbActivity performance view.
Long description: Following dynamic parameters must be enabled: ‘enable monitoring’,‘per object statistics active’, ‘object lockwait timing’
Version: 1.2
Default schedule: 6,16,26,36,46,56 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version > ‘12′ & hasengine=‘YES’ & monitoring_statistics_enabled = ‘1′]

Parameters

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

Job Summary

select sum(LogicalReads) as Total_Logical_Reads, sum(PhysicalReads) as Total_Physical_Reads, sum(PhysicalWrites) as Total_Physical_Writes from master..monTempdbActivity

Table Structures

Table Name Description
dbw_temp_cache_tab Stores latest cache statistics including logical reads, physical reads, and physical writes along with the historical date.
dbw_temp_cache_histr_tab Records historical cache statistics to analyze trends over time, including differences and time intervals between records.

Dependencies

Cleanup Mechanism

In the event of job failure, the following cleanup actions will be executed:

drop procedure dbw_temp_cache_stat_proc
drop table dbw_temp_cache_histr_tab

Implementation Details

The main procedure involved in this job, dbw_temp_cache_stat_proc, performs several actions to monitor and analyze cache usage:

Visualization and Reporting

The job provides a chart with a historical view of the cache usage:

select histr_date "Date", logical_reads_dif/time_interval_sec "Logical reads", physical_reads_dif/time_interval_sec "Physical reads", physical_writes_dif/time_interval_sec "Physical writes" from dbw_temp_cache_histr_tab where time_interval_sec > 0 order by histr_date
* The above SQL feed populates a category chart showing temporal alterations in cache statistics, which aids DB administrators in visual performance monitoring.

Considerations for Installation

[type='instance' & databasetype='sybase']/instance[maj_version > '12' & hasengine='YES' & monitoring_statistics_enabled = '1']
* This job is only compatible with Sybase database instances meeting specific version and configuration requirements as detailed above.