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.