Session statistics
Job details
| Name: | Session statistics | |
| Platform: | Sqlserver | |
| Category: | Performance | |
| Premium package: | SQL Performance package | |
| Description: | Collects session statistics. | |
| Long description: | Collects session performance statistics from system dynamic management views: sys.dm_exec_sessions, sys.sysprocesses | |
| Version: | 2 | |
| Default schedule: | 0 * * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2012′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| history threshold | 7 | The maximum number of day to kept statistics for in the historic tables. |
| collect internal statistics | 30 | How often (in minutes) size of internal objects should be checked. “Session statistics” job collects a large amount of data in the dbWatch schema tables, so it is important to keep track of space usage in the dbWatch database. |
| return status | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when reached maximum space consumption (parameter “Session repository max size”) |
| session repository max size | 500 | Maximum space consumption (in MB) for internal/repository objects that contain session statistics. |
| exclude logins | List of logins to be excluded. | |
| exclude hosts | List of hosts where sessions will be excluded from statistics collection. | |
| exclude programs | List of programs where sessions will be excluded from statistics collection. |
Job Summary
- Purpose: The purpose of this job is to monitor and manage session statistics within a database, specifically handling tasks like tracking active and inactive sessions, checking session repository sizes, and collecting internal statistics.
- Why: This job is essential for maintaining optimal database performance by tracking session activities, handling storage requirements efficiently, and identifying potential issues related to session statistics. It helps in proactive management of database resources.
- Manual checking: You can check this manually in the database by querying session-related dynamic management views and tables to obtain current statistics, active sessions, and check repository sizes.
Key Functionalities
- Session Tracking: Monitors both current and historical session data allowing for a detailed analysis over specified intervals.
- Space Management: Regular checks on the session repository size to ensure it doesn’t exceed the predefined thresholds.
- Data Cleanup: Periodic cleaning of historical data based on specific recurrence rules to avoid unnecessary storage consumption.
Parameters and Thresholds
| Name | Default Value | Description |
|---|---|---|
| history threshold | 7 | Maximum number of days to keep statistics in the historic tables. |
| collect internal statistics | 30 | Frequency (in minutes) for checking the size of internal objects. |
| return status | 1 | Returns a specific status value when maximum space consumption is reached. |
| session repository max size | 500 | Defines the maximum space (in MB) for the session statistics repository. |
| exclude logins | (empty) | Specifies which login names to exclude from statistics collection. |
| exclude hosts | (empty) | Specifies host names to exclude from statistics tracking. |
| exclude programs | (empty) | Specifies program names to exclude from the data collection process. |
Dependencies
- The task involves multiple dependencies on various internal stored procedures and tables managed within the dbWatch schema, aimed to support comprehensive session statistics management.
Presentations and Reporting
- The system is configured to provide detailed reports on internal object sizes, session activities, and SQL command histories.
- Reports are designed to be clear and provide actionable insights with the help of distinct counters and charts that plot critical statistics over time.
Cleanup and Maintenance
- In case of job failure, specific cleanup operations are triggered to remove all potentially corrupted or incomplete data, ensuring the integrity of monitoring metrics.
- The job has a slated replacement and maintenance protocol upon installation to update existing tables and procedures, ensuring enhancements and bug fixes are applied seamlessly.
System Requirements and Compatibility
- The job is configured to run on instances using SQL Server 2012 or later, including Azure SQL Managed Instances, ensuring broad compatibility across different environments.
This monitoring job is crucial for database administrators who need to maintain performance and manage resources effectively by having detailed insights into the sessions running on their databases.