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
- Purpose: The purpose of this monitoring job is to aggregate and report on performance statistics for SQL Server sessions, specifically targeting aspects like session count, CPU time, logical reads, and more on a per-database basis. This includes handling historical data to analyze trends over time.
- Why: This job is crucial for keeping track of session-related performance metrics, which can help in proactively managing database performance and capacity planning on SQL Server platforms, particularly for instances where specific databases may have high loads. By monitoring sessions, users can identify potential resource bottlenecks or inefficient application behaviors that could impact overall server performance.
- Manual Checking: You can check similar information manually by querying the SQL Server dynamic management views and system views involved. Here’s an example command you might run:
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
- Task Object: dbw_ses_db_proc – A stored procedure responsible for data collection and analysis on session-related metrics.
- Job Category: Performance – Indicates that the job is intended to assist in performance optimization and resource management.
- SQL Server Versions: Applicable to SQL Server (version greater than 2005) and Azure SQL Managed Instance, demonstrating compatibility with modern SQL platforms.
Scheduling
- Scheduled to run every 10 minutes as given by its default schedule configuration, ensuring frequent updates and near-real-time data availability.
Dependencies and Data Handling
The task leverages several dependencies to manage state and historical data:
- dbw_ses_db_proc – Main procedure for task execution.
- dbw_ses_db_histr – Used for maintaining current statistics in a historical context.
- dbw_ses_db_last – Captures the most recent aggregate counter values for sessions.
- avg_db_logical_reads_view and avg_db_prog_logical_reads_view – Views to support reporting by averaging logical reads over time.
Implementation Details
- The stored procedures and views are designed to capture and process session data at regular intervals, storing this in historical tables that facilitate trend analysis and immediate state checks.
- Data retention: Controlled by a parameter to specify the number of days for which statistics are kept, impacting storage requirements and historical data availability.
Upgrade Paths
- The task definition includes steps for upgrades from earlier versions by modifying database objects like tables and procedures to adapt new measurements or schema adjustments.
- Specific version upgrades ensure that the monitoring job remains aligned with changes in the database engine’s capabilities or adjustments in monitoring strategies.
Reporting Templates
Included are templates for various types of reports:
- Session per Database: Summarizes total session counts per database.
- Session History per Program: Provides insights into session distribution across different programs.
- Logical Reads and CPU Usage: Detailed statistics on the number of logical reads and CPU usage per session, per database, valuable for identifying heavy operations and potential optimizations.
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.