High activity monitor
Job details
| Name: | High activity monitor |
| Platform: | Sqlserver |
| Category: | Performance |
| Description: | Collects SQL instance CPU usage, CPU usage of other processes, logical and physical reads, and active processes count on the SQL Server instance. |
| Long description: | Collects SQL instance CPU usage, CPU usage of other processes, logical and physical reads, and active processes count on the SQL Server instance. |
| Version: | 2 |
| Default schedule: | 0 * * * |
| 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 | 48 | The number of hours the statistics are hold in the history table. |
| logical reads threshold | 250000 | Avg. logical reads per second. If this threshold is reached over a period of time (controlled by parameter “period span”), a warning/alarm is returned. |
| alarm threshold | 60 | A period of time (in minutes) which must be passed (combined with average values ??exceeding the threshold values) before an alarm is returned by the alert procedure. |
| SQL Server CPU threshold | 85 | Avg. CPU usage by SQL Server. If this threshold is reached over a period of time (controlled by parameter “period span”), a warning/alarm is returned. |
| other CPU threshold | 60 | Avg. CPU usage (excluding SQL Server). If this threshold is reached over a period of time (controlled by parameter “period span”), a warning/alarm is returned. |
| physical reads threshold | 20000 | Avg. physical reads per second. If this threshold is reached over a period of time (controlled by parameter “period span”), a warning/alarm is returned. |
| active processes threshold | 25 | Avg. active processes. If this threshold is reached over a period of time (controlled by parameter “period span”), a warning/alarm is returned. |
| warning threshold | 15 | A period of time (in minutes) which must be passed (combined with average values ??exceeding the threshold values) before a warning is returned by the alert procedure. |
| enable warnings and alarms | NO | If set to “NO” (default), the alert will only collect performance statistics without returning status warning or alarm. |
Job Summary
- Purpose: The job is designed to monitor and report on various performance metrics within a Microsoft SQL Server environment, specifically for SQL Server 2005 instances. Its main focus is to collect data regarding CPU utilization, logical and physical reads, and active sessions, aiding in performance tuning and proactive diagnosis of issues.
- Why: Monitoring these performance metrics is critical in maintaining the health and efficiency of the SQL Server. High CPU usage, high read activity, or excessive number of active sessions can indicate underlying problems or potential bottlenecks in the system.
- Manual Checking: You can check these metrics manually in the database by executing specific SQL commands within the SQL Server Management Studio, typically querying system views or DMVs (Dynamic Management Views).
Detailed Explanation
- The job uses a combination of table creation, data insertion, and function implementations to monitor various performance aspects:
- Data tables like “high_act_mon_tab” and “high_act_mon_last” store real-time and historical data.
- Functions such as “dbwf_data_cache_ratio” and “dbwf_logical_reads” calculate specific metrics over configurable time periods, aiding in threshold-based alerting or reporting.
Dependencies
The job relies on several objects and predefined conditions:
- “high_act_mon_proc”: A stored procedure pivotal in orchestrating the monitoring activity.
- “high_act_mon_tab”, “high_act_mon_last”, “high_act_mon_tab_histr”: Tables that hold real-time data, last fetched data, and historical averages respectively.
Parameters
The job contains adjustable parameters guiding its operation such as thresholds for various metrics (CPU usage, logical reads, etc.), history retention settings, and the decision whether to activate alerts based on the readings.
Monitoring and Alerts
- The system is designed to either collect data silently or generate alerts based on specified conditions and thresholds, which can be configured via the “enable warnings and alarms” parameter.
- Historical data is evaluated to provide insights on average values, which are compared against thresholds to potentiate warnings or alerts.
Implementation details:
- “create table” and “insert into” statements for setting up and populating necessary database objects.
- Prepared SQL functions calculate various metrics and detect significant changes over time.
- The main procedure (“high_act_mon_proc”) conducts the monitoring logic, checking current against past data and administering alerts accordingly.
Performance Reports
- The job enables generation of visual reports categorized into:
- CPU Utilization: A graph representing SQL Server and other CPU usages over the most recent two-hour window.
- Logical and Physical Reads: Displaying data IO metrics to assess the intensity of read operations.
- Active Sessions: Showing the count of active sessions illustrating the concurrency level the server is handling.
Enhancements from previous versions
- The latest updates include additional parameters for finer control over alerts and improved historical data handling, making the monitoring more accurate and aligned with real-world operational demands.