Data hit ratio

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor the buffer cache hit ratio for a SQL Server database. This is done by extracting counter values related to the buffer cache from the sysperfinfo table.
• Why: Monitoring the buffer cache hit ratio is crucial because it assesses the percentage of pages found in the buffer cache without needing to revert to disk reads. High ratios typically indicate good performance as it reduces the need to access the physical disk, thus enhancing the speed and efficiency of data retrieval within the database.
• Manual checking: You can manually check the buffer cache hit ratio by querying the sysperfinfo table in SQL Server as follows:

SELECT cntr_value AS 'Buffer cache hit ratio' FROM master.dbo.sysperfinfo WHERE counter_name = 'Buffer cache hit ratio'

Implementation Details

The job is implemented through various SQL statements that:

• Create necessary tables to store historical data and aggregated statistics.
• Implement procedures for monitoring and calculating hit ratios.
• Insert and update the data within these tables based on the incoming periodic data from sysperfinfo.

Here is a brief overview of the tasks carried out by the SQL procedures:

1. Monitoring increases in logical read periods and updating corresponding statistics.
2. Aggregating and recording average, maximum, and minimum hit ratios and logical reads for distinct time intervals (whole day, working hours, non-working hours, evenings).
3. Handling database maintenance tasks such as cleaning historical data based on the configured retention period.

Dependencies and Database Objects

The job setup involves several database objects including tables and stored procedures created in the SQL Server instance to support the data gathering and reporting functionality. Key object dependencies include:

• master.dbo.sysperfinfo: External dependency on this system table to fetch performance counters.
• Various custom tables like dbw_data_hit_ratio_histr, dbw_data_hit_ratio_aggr, and dbw_reads_increase_periods to store history and aggregate metrics.
• Stored procedures such as dbw_data_cache_reads_increase and dbw_data_hit_ratio, designed to compute and manage cache hit ratio data collection and performance analysis.

Job Scheduling

The job is configured to run at a regular interval:

• Default Schedule: Every 10 minutes, (as per the 0,10,20,30,40,50-minute mark each hour).
• This schedule ensures that data is consistently updated, providing near real-time monitoring of database performance metrics.

Reporting and Presentation

The output from this monitoring job can be visualized and reported traditionally via SQL Server reporting services or other third-party tools that can consume SQL data. Reporting templates defined as part of the dbWatch task facilitate easy visualization content that includes:

• Logical reads history
• Data cache hit ratio over time
• Aggregated views for averages, maxima, and minima across different time frames and categories.

The reports use both tabulated data views and graphical presentations (charts) based on the stored statistical data, providing DBAs with clear insights into performance trends and helping in proactive database management.