Deadlocks event collector
Job details
| Name: | Deadlocks event collector |
| Platform: | Sqlserver |
| Category: | Availability |
| Description: | Collects deadlocks event statistics from Microsoft SQL Server Extended Event Log files. If configured a warning or an alarm can be triggered when a deadlock is detected. |
| Long description: | SQL Server Extended Events has a configurable architecture that allows to collect information to troubleshoot or identify a |
| Version: | 2 |
| Default schedule: | 0 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/.[maj_version > ‘2012′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| history threshold | 180 | The number of days before the history record will be removed from the history table. |
| return status | 0 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when a deadlock is detected. |
| target | c:\tmp | Path value where the target file will be created. |
Job Summary
- Purpose: The primary focus of this job is to monitor and capture deadlock events within SQL Server databases using the dbWatch Control Center. It collects statistics about the deadlocks that occur, helping administrators to identify, troubleshoot, and resolve concurrency issues that may impact database performance and stability.
- Why: This job is crucial because it provides insights into potential deadlock scenarios, which are situations where two or more transactions permanently block each other by each holding a lock on a resource the other transactions are trying to lock. Monitoring deadlocks helps to maintain smooth operational performance and avoid prolonged blocks in the system that can lead to significant transaction delays or failures. If these issues are left unchecked, they can result in severe impacts on the end-user experience and productivity.
- Manual checking: To manually check for deadlock events in the database, the following SQL command can be issued in the SQL Server Management Studio (SSMS):
SELECT * FROM sys.system_health WHERE event_type = 'error' AND state = 'locked'Dependencies and Implementations
- The job has several key components and dependencies for its functioning:
** Stored Procedures and Tables: This includes procedures to create an event session, to load deadlock events from the SQL Server Extended Events (XEL) files, and staging tables to store the data temporarily for processing.
** Cleanup settings: It ensures that any failed dependency operations trigger a cleanup, maintaining the hygiene of the system.
- In terms of implementation:
** The job uses SQL Event Sessions to capture deadlock events into XEL files.
** These events are then periodically polled by the specified stored procedures, parsed, and their data is stored in the reporting tables.
Monitoring and Reporting Features
- The job produces a report detailing deadlock events, which includes information such as the time of deadlock, the processes involved, the transaction names, and the specific resources each process was waiting on.
- Adequate logging and reporting mechanisms are in place to provide visibility into the operational status of the event monitoring, including successes and failures of events capture and the specific errors, if any.
- Parameters like ‘history threshold’, ‘return status’, and ‘target’ for the storage path are configurable and play a crucial role in the behavior of the monitoring process.
Reporting Presentations and Data Insights
- The job generates reports that include:
** A list of recent deadlock events along with detailed statistics about each involved process.
** Information about server sessions, including startup states and configurations.
** Status of the procedure execution, which has implications on event detection and collection latency.
- These reports aim to provide actionable insights into deadlock events so database administrators (DBAs) can take proactive steps to mitigate these issues. This alleviates potential bottlenecks and ensures that the database system runs efficiently without interruptions caused by deadlocks.
Usage and Application
- The job should be scheduled to run at intervals that align with the organization’s operational requirements and database usage patterns.
- Notifications and alerts can be configured to inform the DBA when critical thresholds are breached, indicating severe deadlock scenarios that require immediate attention.
In conclusion, this dbWatch job is an essential tool in a DBA‘s arsenal to ensure that deadlock-related performance issues are promptly identified and resolved, thus maintaining optimal performance and stability of the SQL Server databases.