SQL Workload collector
Job details
| Name: | SQL Workload collector | |
| Platform: | Sqlserver | |
| Category: | Performance | |
| Description: | Collects key SQL Server statistics counters, including Batch Requests/sec, SQL Compilations/sec, and SQL Re-Compilations/sec. | |
| Long description: | Collects key SQL Server statistics counters, including Batch Requests/sec, SQL Compilations/sec, and SQL Re-Compilations/sec. | |
| Version: | 1.1 | |
| Default schedule: | 1,6,11,16,21,26,31,36,41,46,51,56 * * * | |
| 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 | 10 | Number of days to provide statistics for. |
| keep aggregated statistics for | 360 | Removes aggregate statistics older than this threshold (in days) from the tables dbw_sql_workload_collectorr_aggr and dbw_sql_re_compilations_exp_aggr2. |
Job Summary
- Purpose: The purpose of this job is to collect and analyze key statistical counters from SQL Server related to workload performance, including Batch Requests/sec, SQL Compilations/sec, and SQL Re-Compilations/sec.
- Why: The job is crucial for monitoring SQL Server’s performance to ensure efficient query processing and optimizing system resources. By tracking these metrics, database administrators can identify performance bottlenecks and areas for improvement in SQL query execution and compilation.
- Manual checking: You can manually check these metrics in SQL Server using the following SQL command:
bc.. SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name IN (‘Batch Requests/sec’, ‘SQL Compilations/sec’, ‘SQL Re-Compilations/sec’);
Details
- Performance Indicators Tracked:
- Batch Requests/sec
- SQL Compilations/sec
- SQL Re-Compilations/sec
- Maintenance Activities:
- Aggregates these statistics daily, summarizing average, maximum, and minimum values.
- Data for regular and aggregated statistics is preserved for specific periods, as parameterized in the job’s settings.
Implementation
This section outlines the SQL procedural implementation handled by the monitoring job:
- Creation of tables to store current statistics, historical data, and aggregated values.
- A SQL Server stored procedure (dbw_sql_workload_collector_proc) compiles and processes these statistics periodically.
- Data handling involves:
- Inserting new data into the various tables
- Cleaning up old data based on predefined retention periods
- Calculating differences between current and previous data points for trend analysis.
Report Generation
The job facilitates the generation of reports showing statistics over time, which are pivotal for trend analysis and performance monitoring:
- “Database Workload Statistics”: Visualizes Batch Requests/sec, SQL Compilations/sec, and SQL Re-Compilations/sec in graphical format for quick assessment.
- “Aggregated Statistics”: Provides insights during specific periods of the day (like working hours and night hours), aiding in understanding usage patterns and peak activity times.
Dependencies
This job relies on the presence and accessibility of several system views and procedural elements within SQL Server:
- sys.dm_os_performance_counters: A system object providing access to vital performance data.
- Procedure and table structures specifically designed for collecting and storing SQL workload statistics.
Each component must function correctly for the monitoring job to execute without errors and provide accurate and valuable data analyses.
Upgrade Notes
From version 1.0:
- Enhanced procedures for handling data capture, storage, and cleanup.
- Adjustments to improve efficiency and ensure the latest metrics are considered for trending and immediate analysis needs.
Compatibility
This job script is compatible with SQL Server instances (from version 2005 onwards) and Azure SQL Managed Instances, wherein it checks for specific engine editions and versions to ensure proper installation and configuration.