Sessions per database


Job details

Name: Sessions per database
Platform: Sqlserver
Category: Performance
Description: This task returns aggregate performance statistics based on sessions connected to the SQL Server instance.
Long description: This task returns aggregate performance statistics based on sys.dm_exec_sessions dynamic management view and sysprocesses view. The views contains one row per connection, and the lifetime of the rows are tied to connection itself. When a session is disconnected/terminated, the corresponding row are eliminated from this views which might produce inaccurate results if there are sessions disconnected between sampling of statistics by the dbWatch task.
Version: 1.4
Default schedule: 0,10,20,30,40,50 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & 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 statistics for.

Job Summary

SELECT db_name(sp.dbid), count(*) as session_count, sum(es.cpu_time) as total_cpu FROM sys.dm_exec_sessions es JOIN sys.sysprocesses sp ON es.session_id = sp.spid GROUP BY db_name(sp.dbid);

Job Components

Scheduling

Dependencies and Data Handling

The task leverages several dependencies to manage state and historical data:

Implementation Details

Upgrade Paths

Reporting Templates

Included are templates for various types of reports:

The combination of these detailed reports and regular updates provides database administrators with critical insights for performance tuning and problem diagnosis, contributing to a stable and efficient database environment.