Data cache statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and collect data cache statistics in Sybase database instances to ensure optimal performance and efficiency in data handling.
• Why: This job is important as it helps in identifying performance bottlenecks related to data caching which can greatly affect the application’s performance and user experience on platforms that utilize Sybase databases. Efficient data caching minimizes disk I/O by keeping frequently accessed data in memory, hence, monitoring these metrics allows for proactive performance tuning.
• Manual checking: You can manually check data cache statistics in a Sybase database by using the following SQL commands:

SELECT * FROM master..monDataCache;

Report Template Details

• Title: Procedure cache check
• Description: Tracks and presents historical data on cache usage for procedures.
• Frequency: Reports are generated every hour.
• Presentation:
  • Chart Title: Data cache
  • Chart Description: The chart below shows data cache usage history statistics.
  • Data Columns:
    • Date – Indicates when the statistics were recorded.
    • Logical reads per sec. – Number of logical reads per second.
    • Physical reads per sec. – Number of physical disk reads per second.
    • Physical writes per sec. – Number of writes to the physical disk per second.

• Chart details:
  • Categories plotted: Logical reads, Physical reads, Physical writes
  • Chart type: Line chart (indicated by chart-type ‘1′)

Task Implementation and Dependencies

• Implementation Code snippets:
  • Creation of tables to store statistics:

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 ...

• Dependencies:
  • Tables:
    • master..monDataCache – Must exist for data collection.
    • dbw_data_cache_tab – Helper table.
    • dbw_data_cache_histr_tab – Historical data table for data cache.

• Cleanup procedure on task failure:

drop procedure dbw_data_cache_stat_proc

drop table dbw_data_cache_histr_tab

Installation Details

• Forceful Installation: This task will forcefully install databases to ensure dependencies are met and proper functioning.
• Express Installation: True – Indicates that the task will be quickly installed with minimal prompts or steps.

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.