Database session load

Job details

Name:	Database session load
Platform:	Sqlserver
Category:	Performance
Description:	Shows the number of connections over time per database, host and application.
Long description:	Each time the procedure is executed, statistics are collected on the number of connections per database, host, program and login. Then (once a day) the maximum connection and average connections (per database, host,
Version:	1.1
Default schedule:	0,20,40 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
keep data for	3	Number of days to provide detailed statistics for.
ignore databases		List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
ignore hosts		List of hosts (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
ignore programs	dbwatch%	List of programs (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
ignore logins		List of logins (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
keep aggregated data for	14	Number of days to provide aggregated statistics for.

Job Summary

Purpose: This job is designed to monitor and gather statistics on the number of connections over time to each database, categorized by database, host, application, and login.
Why: It helps in assessing the database performance and potential bottlenecks caused by excessive simultaneous connections. It facilitates capacity planning and helps in identifying abnormal behaviors that might hint at application issues or potential security threats.
Manual checking: You can manually check current session loads by querying system tables in SQL Server. Additional specific checks can be obtained by reviewing the SQL procedures embedded in the job code.

Job Function

Data collection: Every execution collects current session details from system tables like master.dbo.sysprocesses.
Aggregation: Daily aggregation of session data is performed to calculate the average and maximum number of connections for each group.
History and Cleanup: Maintains detailed logs for a predefined number of days and aggregates session statistics for slightly longer periods to track trends over time.
Ignored Entities: Allows ignoring specific databases, hosts, programs, and logins for more focused monitoring.

Dependencies

Dependency Object	Type	Description
master.dbo.sysprocesses	System Table	Used for retrieving ongoing sessions from the server.
dbw_db_session_load_histr	Historic Table	Stores historical data for individual sessions.
dbw_db_session_load_aggr_histr	Historic Table	Stores aggregated session statistics.
dbw_db_session_load	Task	Dependent on itself indicating it uses data collected by its own previous runs.

Implementation Details

This job includes SQL scripts to create necessary tables to store session details and their aggregates.
Procedures perform:
- Data extraction from system views,
- Calculations for daily maximum and average connections,
- Maintenance tasks such as the removal of outdated entries.
The job has embedded error handling to update task statuses in case of failures.

Reporting

“Session Load Today”: Provides today’s max and average session counts per database, host, program, and login.
“Aggregated Session Load History”: Gives historical views of session loads enabling trend analysis and longer-term database performance assessments.

Both reports support dynamic querying to always reflect the most recent complete data, and are crucial for diagnosing immediate issues as well as strategic planning.

This job configuration and its automation help maintain optimal performance and aid in proactive database management within SQL Server environments.