Transactions load
Job details
| Name: |
Transactions load |
| Platform: |
Sqlserver |
| Category: |
Performance |
| Description: |
Shows the transactions load over time, total and top 5 databases. |
| Long description: |
Shows the transactions load over time, total and top 5 databases. |
| Version: |
1.2 |
| Default schedule: |
1,11,21,31,41,51 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘1999′ & 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. |
Job Summary
- Purpose: The purpose of this job is to monitor and report on the transactions load for instances of Microsoft SQL Server and Azure SQL Managed Instance.
- Why: This job is important to ensure smooth performance and immediate response to potential slowdowns due to unusual transaction load on the selected platforms. Identifying over-usage of transactions can help in optimizing resource allocation and preemptively solving potential bottlenecks or failures.
- Manual checking: You can check this manually in the database by issuing these SQL commands:
SELECT cntr_name, cntr_value_dif, cntr_time_period, histr_date FROM dbw_transactions_load_histr ORDER BY histr_date ASC;
Task Details
- Name: Transactions load
- Description: Monitors and records the transaction loads over time, specifically recording total and top 5 database loads.
- Implementation:
- The task involves creating tables for storing historical transaction data and aggregating this data over time to calculate average values per minute.
- A stored procedure (dbw_transactions_load_proc) handles data collection, historical data pruning (based on a configurable number of days), and updating transitional states of data.
Dependencies and Execution
- Dependencies:
- System table: master.dbo.sysprocesses
- Historical tables: dbw_transactions_load_histr, dbw_transactions_load_aggr_histr
- Help table: dbw_transactions_load
- Self-dependency: Transaction load task relies on itself for updating data.
- Execution Details:
- The code inserts new transaction data collected from the sysperfinfo table in SQL Server.
- It computes differences between current and previous historical data to understand transaction load over time.
- Handles data retention by deleting older records based on the defined ‘keep data for’ period.
Reporting
- Title: Transaction Load
- Report Structure:
- Presents transaction data for top 5 databases and the total by individual record and averages per minute.
- Includes visual representation through category charts mapping transactions over time against respective dates.
Enhancement from Previous Versions
- Version: 1.2 is an enhanced version that includes updated procedures for efficient transaction load calculation and possibly enhanced error handling or data logging mechanisms from version 1.1.
Scheduled Execution
- Default Schedule: The task is scheduled to run every 10 minutes starting from the first minute of the hour.
- This frequent execution schedule ensures real-time monitoring and quick response to transaction load changes.
Conclusion
This dbWatch job plays a crucial role in maintaining and enhancing database performance by continuously monitoring transaction loads on critical SQL Server and Azure managed instances. The information provided by this job assists in proactive management and scaling decisions.