Memory statistics

Job details

Name:	Memory statistics
Platform:	Sybase
Category:	Performance
Description:	Collects memory statistics based on the dbWatch tasks ‘System monitor collector’ which runs the ‘dbcc monitor’ utility continuously.
Long description:
Version:	1.3
Default schedule:	3,13,23,33,43,53 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version = ‘12′ & hasengine=‘YES’]

Parameters

Name	Default value	Description
history threshold	48	The amount of hours the statistics will be hold in the history table.

Job Summary

Purpose: The purpose of this job is to monitor and collect memory usage statistics for Sybase database instances.
Why: This job is important to ensure optimal performance and to preemptively address memory-related issues which can lead to system slowdowns or crashes. By actively monitoring memory usage, administrators can make informed decisions about resource allocation and system tuning.
Manual checking: You can check this manually in the database by issuing the following SQL commands:

select field_name, value, histr_date from dbw_memory_stat_histr order by histr_date desc

Job Details

Name: Memory statistics
Object Name: dbw_memory_stat_proc
Description: Collects memory statistics using the dbWatch tasks “System monitor collector”, which continuously runs the “dbcc monitor” utility.
Version: 1.3
Company: dbwatch.no
Default Schedule: The job runs every 10 minutes (at 3, 13, 23, 33, 43, and 53 minutes past each hour).

Dependencies

The job has several dependencies, which are crucial for its operation:

dbw_sysmon_collector: Collects statistics by running the “dbcc monitor” utility. This task must be installed first and is critical for gathering memory statistics.
This also includes an internal dependency on the “dbw_memory_stat_proc” procedure itself.
dbw_memory_stat_histr: A table required for storing memory statistics history.

Cleanup on Failure

In case of failure, the following cleanup operations are defined:

drop procedure dbw_memory_stat_proc
drop table dbw_memory_stat_histr

These ensure that failed or incomplete tasks do not leave residual data or procedures that could affect future operations.

Reporting

The following reports are generated by the job:

Title	Chart Type	Data Shown	SQL Command
Memory pages	Line Chart	Pages per minute	“select field_name, value/(elaps_time/60.) value, histr_date from dbw_memory_stat_histr where field_name like ‘mempages%’ and elaps_time/60.>0 order by histr_date asc”
Memory heap (allocated and freed)	Line Chart	Memory heap changes per minute	“select field_name, value/(elaps_time/60.) value, histr_date from dbw_memory_stat_histr where field_name like ‘memheap_%’ order by histr_date asc”
Memory heap calls	Line Chart	Calls to memory heap per minute	“select field_name, value/(elaps_time/60.) value, histr_date from dbw_memory_stat_histr where field_name like ‘%heap_calls’ order by histr_date asc”

Implementation Details

Upon installation, the job creates a table dbw_memory_stat_histr for storing historical data and a stored procedure dbw_memory_stat_proc which contains the logic for data collection, summarization, and cleanup based on a configured history threshold. The stored procedure is executed automatically based on the default schedule and is integral for ongoing data capture and analysis.

This setup is pivotal in facilitating proactive database management, ensuring that memory usage is efficiently tracked and managed, thus maintaining system stability and performance.