System monitor collector

Job details

Name:	System monitor collector
Platform:	Sybase
Category:	Performance
Description:	This task collects statistics by running the ‘dbcc monitor’ utility which is also a part of the stored procedure sp_sysmon. It is important that the ‘System monitor collector’ is not used while the sp_sysmon procedure is executed from some where else, as this will clear all the counters, resulting in incorrect statistics. After successful execution of this procedure, the statistics are extracted from the master.dbo.sysmonitors table and stored in a local table (dbw_sysmon_stat) where they can be analyzed by other dbWatch performance tasks.
Long description:
Version:	1.5
Default schedule:	* * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version = ‘12′ & hasengine=‘YES’]

Parameters

Name	Default value	Description
time interval	00:05:00	time interval for running the sp_sysmon procedure.
group names	engine%,buffer_%,kernel,disk_%,memory	Group names to which the counters belongs to. Only statistics (field_name, group_name, field_id and value) from groups specified by this parameter will be extracted from the master.dbo.sysmonitors table.
sample performance stats	YES	If “YES“ (default), the procedure will run the DBCC MONITOR utility (in “clear”, “sample” and “select” mode) which collects data into the master.dbo.sysmonitors table. If “NO”, the procedure checks for new statistics in the master.dbo.sysmonitors table without running the DBCC utility (typically when there is another program running the sp_sysmon procedure).
estimated time interval	60	When collecting statistics from master.dbo.sysmonitors table (“sample performance stats” parameter set to “NO”) the interval for statistics is extracted from the “clock_ticks” counter. However if the sp_sysmon procedure is active (is running), the “clock_ticks” counter value is set to 0. In that case the procedure will estimate the time interval using this parameter value (default 60 sec.).

Job Summary

Purpose: The purpose of this job is to collect statistics from the Sybase database instance using the “dbcc monitor” utility, part of the sp_sysmon stored procedure, and store these statistics in a local table for analysis.
Why: This job is crucial for monitoring performance metrics in Sybase databases. It ensures that critical performance data is collected regularly, allowing for proactive database management and optimization. The job avoids the simultaneous execution of sp_sysmon from multiple sources, which can corrupt performance data.
Manual checking: To check the performance statistics manually, use the following SQL commands:

SELECT * FROM master.dbo.sysmonitors;

SELECT * FROM dbw_sysmon_stat;

SELECT * FROM dbw_sysmon_last_run;

Job Description and Details

This monitoring job is designed specifically for Sybase database instances. It runs a utility called “dbcc monitor,” which is integral to the stored procedure sp_sysmon. Special considerations are taken to ensure that no other instances are running sp_sysmon simultaneously as it would reset the counters, leading to inaccurate data.

The collected data is stored in the table ‘dbw_sysmon_stat’, and the time and statistical updates are logged into ‘dbw_sysmon_last_run’. This setup helps in maintaining a history of performance metrics, which is crucial for trend analysis and diagnostic purposes.

Implementation Details

The job involves several steps in its implementation:

1. Creation of necessary tables to store statistical data and historical runs.
2. Execution of the sp_sysmon via dbcc monitor commands depending on whether performance stats are to be sampled or just selected.
3. Conditional handling based on existing counters to ensure data integrity and prevent duplicity in sampling.
4. Storing results from master.dbo.sysmonitors into ‘dbw_sysmon_stat’ based on predefined group names and conditions to filter out irrelevant data (e.g., excluding disk counters with zero values).

Dependencies and Cleanup

This job has several self-dependencies such as ‘dbw_sysmon_collector’ itself and tables ‘dbw_sysmon_stat’ and ‘dbw_sysmon_last_run’. In case of job failure, cleanup procedures involve dropping of the mentioned tables and stored procedures to prevent any corruption or leftover data.

Execution Schedule

Default schedule: The job is configured to run every minute, which aligns with the critical nature of performance monitoring in active database environments. This frequent execution ensures that the database administrators receive timely data to react to potential performance issues.

Tabular Reports

The monitoring job generates tables and reports based on the data collected:

Date and Time	Elapsed Time (secs)
Date from ‘dbw_sysmon_last_run’	Time duration from ‘dbw_sysmon_last_run’

These reports provide a quick glance at when the last statistics were collected and how long the collection process took, aiding in quick assessments of performance monitoring operations.

Overall, this monitoring job is vital for maintaining the health and performance of Sybase database instances, ensuring data is collected meticulously and stored for performance analysis and reporting.