Data cache memory usage

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and gather statistics on data cache memory usage across the SQL Server database instances, specifically focusing on the top 10 databases.
• Why: This job is pivotal for database performance management as it helps in tuning and troubleshooting by providing insights on how data cache is utilized by different databases over varying times of the day. Efficient cache usage is critical for the performance of the database server. Early detection of irregular patterns and potential issues can preempt performance degradation.
• Manual checking: You can manually check the data cache memory usage in the database by issuing the following SQL commands:

SELECT db_name_val AS [Database], data_cache_size_val_MB AS [Cache Size (MB)], histr_date AS [Date Recorded] FROM db_cache_mem_usage_tab ORDER BY histr_date ASC;

Job Details and Implementation

• The job targets SQL Server instances with major versions from 2005 to 2008 and checks if they include database engine features, narrowing to specific editions like Microsoft SQL Server or Azure SQL Managed Instance.
• It periodically collects data regarding memory usage allotted to data cache for the top 10 databases.
• The collected data is stored in tables specifically designed for current data values and aggregated values which take into consideration different time periods such as working, night, and evening hours.
• Aggregation of data happens based on the timestamp differences to manage data for days where data is absent and also to maintain consistency in data collection over time.

Dependencies and Structure

• Direct Dependencies:
• Tables for storing the current data cache sizes.

• Cleanup Policy: In event of job failure, the relevant records in dependency tables are cleaned up, ensuring no corrupt data persists.

Job Execution Schedule

• The default scheduling is set for every half-hour mark indicated by the settings ‘20,50 * * *’, showing the job runs twice an hour at the 20th and 50th minute.

Reporting and Analytics

• The job facilitates several reports and visual analytics:
• “Data cache memory usage”: Visualizes total instance memory usage along with data cache size over time for top databases.

Upgrade and Version Management

• When upgrading from version 1.2, specific changes in the procedures handling the data fetch and aggregation are executed to accommodate new logic or improvements in data handling procedures.

Utilizing this job, a database administrator can efficiently manage and optimize the database cache which is critical for system performance, ensuring the SQL Server instances run optimally and are proactively managed.