Session SQL event collector

Job details

Name:	Session SQL event collector
Platform:	Sqlserver
Category:	Performance
Premium package:	SQL Performance package
Description:	This job collects performance statistics from the dbWatchSessionStatsXE Extended Events session. It captures long-running SQL batches and RPC calls along with relevant session and database information. Complements the ‘SQL statistics’ job by adding visibility into who is executing what.
Long description:	This job collects performance statistics from the dbWatchSessionStatsXE Extended Events session. It captures long-running SQL batches and RPC calls along with relevant session and database information.
Version:	1.52
Default schedule:	3,13,23,33,43,53 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’]

Parameters

Name	Default value	Description
history threshold	7	The number of days before the history record will be removed from the history table.
max duration time	200	Max Duration Time specifies the threshold for capturing SQL batches and RPC calls in the Extended Events session. Only queries with execution time equal to or exceeding this value (in milliseconds) are logged, allowing flexible adjustment of monitoring sensitivity.
path		Specifies the full path where event data will be stored. The name of the file is “dbWatchSessionStatsXE”. This file will receive all captured events for later analysis.
max file size	20	Defines the maximum size (in MB) that a single event file can grow to before a new file is created or the rollover policy is applied (must be 11 MB or or higher).
max rollover files	10	Specifies the maximum number of event files to retain. When this limit is reached, the oldest file is deleted to make room for new data.
return status	1	Return status value (ALARM = 2, WARNING = 1, or OK = 0) when either the maximum size of the repository history table (parameter “repository max size”) is reached or the job execution time exceeds the defined threshold (parameter “max execution time”). This value indicates the current state and can be used to trigger alerts or monitor job health.)
repository max size	1000	Defines the maximum size (in MB) of the repository history table before a warning or alarm is triggered.
max execution time	60	Specifies the maximum allowed execution time (in seconds) for the job. If the job runs longer than this threshold, a warning or alert is triggered.
database filter		Specifies the list of databases to which the Extended Events session should apply. The value can be provided as a comma-separated list of database names. Wildcards () are supported to match multiple databases by pattern (e.g., %Sales to include all databases with “Sales” in their name).

Job Summary

Purpose: The purpose of this job is to collect and monitor SQL performance statistics specifically related to sessions and their activities, focusing on long-running SQL batches and RPC (Remote Procedure Call) operations.
Why: This job is crucial for understanding the dynamics of SQL query performance and can aid in identifying queries or batches that consume extensive resources in terms of duration and CPU usage. By associating activity with session-level details, this job helps in pinpointing the cause of slowdowns or abnormal behavior in the SQL Server environment. It’s particularly useful for live troubleshooting and proactive monitoring.
Manual Checking: You can manually check the performance statistics at the SQL Server instance using the following query:
bc. SELECT * FROM ses_sql_event_collector_curr WHERE duration_ms > {threshold};

Key Details and Logic Implemented

Data Collection: The job collects data from the dbWatchSessionStatsXE Extended Events session.
Targeted SQL Server: It targets instances of SQL Server from version 2005 onwards that are using the Microsoft SQL Server engine edition.
Sensitivity Adjustment: Parameters such as ‘max duration time’ allow for the adjustment of thresholds that dictate what constitutes a long-running operation, hence tuning the sensitivity of the monitoring process.
Event Session Management: It includes procedures to handle the creation and management of the SQL Server Event Sessions tailored according to the parameters set (such as path, duration, max file size, etc.), using dynamic SQL built within the job’s stored procedures.
Reporting: The implementation provides detailed internal execution statistics stored and reported in different system tables designed to offer insights on both historical and current session activity data.

Database Structures and Operations

Table Creation: Multiple support tables are used, like “ses_sql_event_collector_curr”, “ses_sql_event_collector_histr”, and “ses_sql_event_coll_internal_histr”, each targeted at holding specific types of data (current sessions, historical data, and meta about the collection process).
Index Management: The job manages indexes on these tables to optimize query performance when analyzing the collected data.
Data Clean-up and Management: Procedures ensure data relevance by periodically cleaning up old data based on ‘history threshold’ settings and managing table sizes to prevent unchecked growth.
Error Handling: Comprehensive error handling in procedures captures and logs errors to aid in troubleshooting and alerts.

Version and Upgrade Details

Version Information: The current version of this job is 1.52, with upgrade paths from previous versions (1.0 to 1.51) included.
Upgrade Logic: Upgrade scripts check for missing parameters or table definitions and apply them without data loss. This ensures that current installations are brought up to date with new functionalities without impacting the existing operational data.

Parameters and Configurations

Performance Parameters: Include settings such as max file size, max rollover files, history threshold, which control the granularity and volume of data captured.
Execution Control: Parameters like ‘return status’, ‘repository max size’, and ‘max execution time’ are used to define thresholds for alerting and job status reporting based on performance and data volume metrics.

Dependencies and External Interactions

External Dependencies: The job relies on SQL Server’s Extended Events and Dynamic Management Views to capture and read event data.
Dependency Management: The clean-up attribute ensures that if the job fails, temporary or partial data is not left behind, maintaining system cleanliness and integrity.

Reporting and Visualization

Data Presentation: The implementation includes visualization options for internal execution history, active sessions, and job execution times, offering users readable and actionable reports.
Custom Reporting: The SQL provided allows for custom reporting needs, ensuring flexibility in how data is reviewed and analyzed by system administrators or database performance analysts.