User connections check

Job details

Name:	User connections check
Platform:	Sybase
Category:	Performance
Description:	Checks the configuration setting of the ‘number of user connections’ from the master.dbo.syscurconfigs table against the number of connections on the instance (processes with suid != 0 in from the master.dbo.sysprocesses table).
Long description:
Version:	1.2
Default schedule:	2,12,22,32,42,52 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version = ‘12′ & hasengine=‘YES’]

Name	Default value	Description
warning threshold	90	The check will give a warning when the connection pool usage is above this ratio (in %).
alarm threshold	95	The check will give an alarm when the connection pool usage is above this ratio (in %).
keep data for	7	The number of days to keep records.

Purpose: The purpose of this job is to monitor and check the usage ratio of user connections on a Sybase database instance, comparing the allocated number against active connections.
Why: This job is important to ensure that the number of active user connections does not exceed the configured limit, which could lead to database performance issues or even unavailability. Monitoring these thresholds allows for proactive management of database resources.
Manual checking: To manually check the user connections status in the database, you can use the following SQL commands:

SELECT config, name, value FROM master.dbo.syscurconfigs WHERE config = 103;

SELECT count(*) FROM master.dbo.sysprocesses WHERE suid != 0;

This monitoring task is structured and deployed through the following steps:

A table called “user_connections_check_histr” is created to store historical data about free and active user connections along with timestamps.
A stored procedure “dbw_user_connections_check” is established to calculate the current ratio of active user connections to the total user connections permitted (as configured).
The procedure checks against predefined warning and alarm thresholds (90% and 95%, respectively) to output appropriate notifications or alerts.
Historical usage data is cleaned up based on a “keep data for” setting which controls the retention period (default is 7 days).

This task has a self-dependency and relies on historical data stored in “user_connections_check_histr” which requires cleanup actions to manage space efficiently.

If the job fails:

The procedure “dbw_user_connections_check” is dropped.
The table “user_connections_check_histr” is also dropped to ensure that erroneous data or partial configurations do not persist.

A report template included in this job facilitates visualizing the data:

It showcases user connection trends over time split by “Used” and “Free” connections.
Data is selected and aggregated from the “user_connections_check_histr” table, presenting a unified view that updates each execution cycle.
The report output includes a category chart to visually represent connection usage over time, enhancing the interpretability of historical connection data.

The chart plots the historical data by date and aggregates by the type of connection data (“Used” versus “Free”).
This helps in easy assessment of how user connections are being utilized over time relative to their maximum configured limit.