Wait statistics
Job details
| Name: | Wait statistics | |
| Platform: | Sqlserver | |
| Category: | Performance | |
| Description: | Collects statistics about all waits encountered by threads that executed. This task is based on the sys.dm_os_wait_stats dynamic performance view. | |
| Long description: | Collects statistics about all waits encountered by threads that executed. This task is based on the sys.dm_os_wait_stats dynamic performance view. | |
| 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’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| keep data for | 5 | Number of days to provide statistics for. |
| keep aggregated data for | 60 | Number of days to provide aggregated statistics for. |
| enable warnings and alarms | NO | If set to “NO” (default), the alert will only collect performance statistics without returning status warning or alarm. |
| alarm threshold (time) | 30 | A period of time (in minutes) which must be passed (combined with average values of total waits) before an alarm is returned by the alert procedure. |
| warning threshold (time) | 10 | A period of time (in minutes) which must be passed (combined with average values of total waits) before a warning is returned by the alert procedure. |
| waits threshold | 80 | Total waits in percent relative to capacity threshold which is calculated from logical CPU count (cpu_count value from sys.dm_os_sys_info table) multiplied by 60 seconds. If this threshold is reached over a period of time (controlled by parameters “warning/alarm threshold (time)”), a warning/alarm is returned. |
| ignore wait types | The list of wait types to be ignored (separated by comma). |
Job Summary
The dbWatch Control Center monitoring job provided focuses on gathering and managing wait statistics in a database environment. This task is vital for assessing database performance issues related to wait times and system resource utilization.
Purpose of the Monitoring Job
- Monitor Wait Times: Essential for tracking the various types of waits that database processes encounter. Helps in identifying performance bottlenecks.
- Manage Data Retention: Controls how long detailed and aggregated data should be retained, allowing for effective performance analysis over time.
- Manage Alarms and Warnings: Provides mechanisms to trigger alerts based on specified thresholds, which facilitate proactive database performance management.
Importance of the Job
- Performance Optimization: By monitoring wait times and understanding their causes, database administrators can take targeted actions to improve system performance.
- Predictive Analysis: Setting thresholds for warnings and alarms helps in predicting potential system issues before they become critical.
- Resource Efficiency: Helps in optimizing the usage of system resources by understanding and managing the load on the CPU and other components.
Manual Checking
To manually check the current wait statistics, use the following SQL commands:
select wait_type, cast(round(sum(wait_time_s/(period/60.))- sum(signal_wait_time_s/(period/60.)),1) AS DECIMAL (16,1)) [Wait time], cpu_count*60 [Limit] from dbw_wait_stat_histr, sys.dm_os_sys_info where histr_date = (select max(histr_date) from dbw_wait_stat_histr) group by wait_type, cpu_count having sum(wait_time_s)- sum(signal_wait_time_s) > 0Expected Output and Reporting
The outputs of this monitoring job are formatted into various reports, visualized through charts, and contextual presentations providing insights into current and historical wait times across the database system. Specific outputs include:
- Current Wait Times: Key statistics on wait types and their current values against calculated thresholds.
- Wait History: Overview of wait time changes over selected intervals, allowing for trend analysis and deeper inspection of historical data.
- Aggregated Statistics: Comparison of data across different time frames such as working hours, evening, and night times, to discern patterns and potential abnormal behavior.
Tables Used and Dependencies
Tables and procedures such as dbw_wait_stat_last, dbw_wait_stat_histr, and dbw_wait_stat_proc among others are crucial for capturing and processing wait data. Dependencies ensure that data integrity and process flow are maintained accurately throughout the task execution process.
Overall, this job is essential in maintaining a high-performance database environment by continuously monitoring and managing wait statistics, which are critical indicators of database health and efficiency.