Session load

Job details

Name:	Session load
Platform:	Sqlserver
Category:	Performance
Description:	Shows the number of active sessions over time.
Long description:	Shows the number of active sessions over time.
Version:	1.1
Default schedule:	0,10,20,30,40,50 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘1999′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
keep data for	5	Number of days to provide statistics for.

Job Summary

Purpose: This job is designed to monitor and track the session load on SQL Server instances. It collects, aggregates, and presents data about active and total sessions over time.
Why: Monitoring session load is critical for maintaining the performance and stability of SQL Server environments. By tracking the number of active and total sessions, the job helps in identifying trends and spikes which could indicate issues or potential bottlenecks.
Manual checking: To manually check the session load, the following SQL command can be issued:

SELECT COUNT(*) FROM master.dbo.sysprocesses WHERE cmd != 'TASK MANAGER'

Technical Description

“dbw_session_load” task captures the current count of active and total sessions from the master database’s sysprocesses table and logs these values into a historical table for trend analysis.
Data is aggregated daily, calculating average, maximum, and minimum values for both active and total sessions, which are also stored in a history table.
The job’s implementation ensures data older than a set number of days (defined by ‘keep data for’ parameter) is purged from the history to maintain database performance and size.
The job executes every ten minutes as specified in the default schedule.

Dependencies

System table master.dbo.sysprocesses: Essential for fetching current session details.
Historic tables dbw_session_load_histr and dbw_session_load_aggr_histr: Used for storing historic data and aggregated statistics respectively.

Reports and Presentations

Task report templates provide various presentations including session load over time, session statistics per day, and the current highest number of sessions per database, host, and login.
Reports include both data tables and graphical charts for easy visual analysis.

Implementation Specifics

SQL procedures to create necessary tables and handling data:
- Creating tables for storing session data and aggregated statistics.
- Procedures to insert and update session load data at scheduled intervals.

Report Templates and Output

Report Title	Description	Example Output
Session load	Shows the number of total and active session over time.	Graph of session numbers over historical dates.
Session history	Presents average, max, and min session statistics.	Data table with averages, maximums, and minimums of session counts.
Sessions per database	Lists top databases by current session counts.	Table with databases and their respective session counts.
Sessions per host	Displays top hosts by current session counts.	Table listing hosts and associated session counts.
Sessions per login	Details top logins by current session counts.	Table showing logins and their session numbers.
Current sessions	Current session details for top active programs and users.	Detailed table of current sessions by program, user, and host.

The job automates data collection and reporting processes critical for database performance monitoring, thus reducing the need for manual checks and enabling quick responsiveness to potential issues.