Database statistics


Job details

Name: Database statistics
Platform: Sqlserver
Category: Availability
Description: This procedure gathers statistics per database.
Long description:
Version: 1.8
Default schedule: 0 5 6 *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’]/instance[databasetype=‘sqlserver’]/.[hasengine=‘YES’]/.[maj_version > ‘1999′ & engine_edition = ‘Microsoft SQL Server’]

Parameters

Name Default value Description
dbwatch databases name dbwatch Name of the dbWatch database where all statistics are collected.

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

Why This Job Is Important

Scope

The job targets SQL Server instances with specific criteria:

Dependencies

Operational Workflow

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

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:

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.