Blocking detector

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and detect any blocked sessions in SQL Server instances, ensuring that potential performance issues related to session blocking are identified early.
• Why: This job is important because session blocks can cause delays and disrupt normal operations on the SQL Server or Azure SQL Managed Instance platforms. Early detection helps in taking necessary measures to mitigate issues before they impact the performance significantly.
• Manual checking: To manually check for blocked sessions in the respective database, you can use the following SQL command:

select RTRIM(cast(round(waittime/1000./60.,2) as decimal(16,2))), RTRIM(status), RTRIM(program_name), RTRIM(cmd), RTRIM(loginame) from sysprocesses where blocked!=0 and waittype != 0x0000 order by 1 desc

Job Configuration

• “Version”: 2
• “Company”: dbwatch.no
• “Category”: Performance
• “Schedule”: Default schedule is every 2 minutes to perform this monitoring task.

Job Execution Logic

• The job extracts data such as wait time, session status, the program name, session command, and login name from the sysprocesses table where sessions are detected as being blocked.
• A status and message are determined based on the retrieved data. The conditions checked are:
  • If wait time of the blocked session exceeds the alarm threshold, an alarm status is set along with a detailed message.
  • If wait time of the blocked session exceeds the warning threshold but is below the alarm threshold, a warning status is set.
  • If no blocking sessions are detected, a normal status is maintained with a corresponding message.
• The job also accumulates count of blocked sessions and includes this in the message if more than one session is blocked.

Thresholds

• “Warning Threshold”: 5 minutes
• “Alarm Threshold”: 30 minutes

Report Structure

• Title: Blocking Detector
• Subtitle: Checks whether there exists any blocked sessions.
• Schedule: Hourly report generation.
• Content: Details reported include session wait time, status, and other details formatted into a table showing blocked session information.
• Details are provided through dbWatch report template configurations that utilize both SQL queries and dbWatch Query Language (DBWQL) to fetch and display results.