Session load

Job details

Name:	Session load
Platform:	Sybase
Category:	Performance
Description:	Shows session statistics.
Long description:
Version:	1.1
Default schedule:	0,10,20,30,40,50 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version > ‘11′ & hasengine=‘YES’]

Name	Default value	Description
keep data for	7	The number of days to keep the data for.

Purpose: The purpose of this monitoring job is to track and maintain historical data regarding the load of database sessions in Sybase instances where the major version is greater than 11.
Why: Monitoring the session load is crucial for understanding how resources are being utilized within the database and for identifying potential performance bottlenecks. By tracking both active and total number of sessions over time, administrators can assess the peak and average utilization, enabling more informed database management and tuning.
Manual Checking: To manually check session load statistics in the database, you can use the following SQL command:

SELECT spid, status, hostname, program_name, cmd, cpu, loggedindatetime FROM master.dbo.sysprocesses WHERE suid != 0 ORDER BY status DESC

The job includes the creation of tables and stored procedures to facilitate the handling and aggregation of data:
- dbw_session_load_histr: Stores total number of process and active processes daily.
- dbw_session_load_aggr_histr: Aggregates the data on a daily basis for metrics such as average, maximum, and minimum session counts both for total and active sessions.

Table Name	Description
dbw_session_load_histr	Holds records for the total and active number of processes captured periodically.
dbw_session_load_aggr_histr	Aggregated session statistics used for reporting and historical analysis.

This job depends on several internal task-related and SQL Server system tables:
- Self-dependency: The task relies on its own previous executions.
- master.dbo.sysprocesses: Utilized for fetching current session statistics like SPID and status.
- Dependency tables include dbw_session_load_histr and dbw_session_load_aggr_histr for historical data storage.

Object	Object Type	Description	Clean-up on Fail
dbw_session_load	2	Main dependency to itself.	Yes
dbw_session_load_histr	0	Dependency table for session data.	Yes
dbw_session_load_aggr_histr	0	Dependency table for aggregated session data.	Yes
master.dbo.sysprocesses	4	SQL Server system table for current sessions.	No

Sessions: Provides a real-time snapshot of session details like SPID, status, and host.
Session Load Over Time: Depicts the total and active session counts over time, giving insight into load distribution through various charts.
Aggregated Session Statistics: Shows aggregated session data such as maximum, minimum, and average counts per day, helping in trend analysis and capacity planning.

Report Section	Visualization Type	Description
Session details	List/Table	Displays current sessions and their statuses.
Session load over time	Line/Bar Chart	Graphical representation of session counts over time.
Aggregated session statistics	Line/Bar Chart	Shows daily max, min, and avg session counts.

This job is scheduled to run at fixed intervals (every 10 minutes) to ensure that session statistics are regularly updated and analyzed. This periodic monitoring helps in maintaining a robust and stable database environment by enabling timely detection and resolution of potential issues related to session handling and performance.