Database statistics

Job details

Parameters

Job Summary

The dbWatch monitoring job “Database statistics” is designed to collect and analyze detailed statistics from Microsoft SQL Server databases, particularly those that meet specific conditions such as having engines installed and being a version later than 1999. This task is crucial for maintaining optimal database performance and availability.

Purpose

• Monitors and gathers critical statistical data across various SQL Server databases.
• Provides insights into how databases are performing, their space utilization, transaction loads, activity levels, and more.

Why This Job Is Important

• Helps in proactively managing database health and identifying performance degradations or anomalies.
• Useful in capacity planning and optimization efforts by monitoring space and usage.
• Ensures that the databases adhere to performance and operational standards by monitoring key metrics and statistics.

Scope

The job targets SQL Server instances with specific criteria:

• Engine must be present.
• Major version must be greater than 1999.
• The database edition should be Microsoft SQL Server.

Dependencies

• The job relies on several aggregated data tables such as ‘dbw_data_hit_ratio_aggr’, ‘dbw_transactions_load_aggr_histr’, and others to fetch historical data for analysis.
• It also depends on procedures like ‘dbw_db_stat’ for executing the main monitoring operations.

Operational Workflow

The “ms_gather_database_statistics_job” is triggered under a default schedule or manual invocation. It consists of the following key steps:

• Check for necessary conditions and versions of dependent tasks and server properties.
• Gather various statistics like logical reads, transactions load, CPU speeds, and backup counts from different system tables and historical data tables.
• Update central statistics tables like ‘dbw_db_stat_tab’ and ‘dbw_db_server_stat_tab’ with gathered data.

Example of Manual Checking
To manually check some of the statistical data collected by this job, one could use SQL commands resembling the following:

SELECT db_name, Allocated_Space_MB, Used_Space_MB, Low_end_activity FROM dbw_db_stat_tab ORDER BY High_end_activity DESC

Impact of the Job

This dbWatch job ensures that database administrators can:

• Keep tabs on the performance and health of their SQL environments without manual checks.
• Quickly react to potential issues highlighted by the statistical trends and data anomalies reported.
• Ensure SQL Server instances are configured correctly and running as expected in terms of performance and resource utilization.

Implementation Details

Checks and updates for various database statistics are implemented via a series of SQL procedures which are part of the dbWatch job package. Here, alterations, checks, and updates made into custom tables such as ‘dbw_db_stat_tab’ and ‘dbw_db_server_stat_tab’ are essential for tracking and maintaining the performance metrics over time. The databases are checked for activity, storage metrics, and configurations, and the results are stored for reporting and monitoring purposes. Aggregated and historical data serve as a benchmark for comparisons and trend analysis.

Overall, this monitoring job by dbWatch provides essential functionality to maintain optimal database operation and performance across SQL Server platforms.