Blocking statistics

Job details

Name:	Blocking statistics
Platform:	Sqlserver
Category:	Performance
Description:	Collects and evaluates current blocking sessions from master..sysprocesses. Raises WARNING or ALARM when blocking wait times exceed configured thresholds, and writes qualifying events to history tables.
Long description:	The procedure scans master..sysprocesses for active blockers (blocked <> 0 and waittype <> 0×0000), materializes results into a temp table, and iterates the rows to evaluate wait time (in minutes). If any session’s blocking duration meets or exceeds the warning or alarm thresholds, the job records a history event with severity and context (SPID/ECID, wait type, database, status, program, command, login). Optionally, via a capture threshold, the job can persist sub-threshold (short-lived) blocking to enable trend analysis even when no alert would fire.
Version:	2.01
Default schedule:	0,5,10,15,20,25,30,35,40,45,50,55 * * *
Requires engine install:	Yes
Compatibility tag:	. .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
warning threshold	5	The maximum number of minutes a session can be blocked before a warning is triggered.
alarm threshold	30	The maximum number of minutes a session can be blocked before an alarm is triggered.
capture threshold	0	Persist blocking sessions even when they do not reach warning/alarm. Any block with duration ≥ this value (minutes) is written to history with OK status. Default: 0 = disabled (legacy behavior; only warning/alarm events are stored). If set below “warning threshold”, you’ll capture short-lived blocks for trending. If set ≥ “warning threshold”, it is effectively redundant (alerts already persist data).

Job Summary

Purpose

Monitors and reports on blocked session statistics in SQL Server databases.
Detects any sessions that may be causing blocks and gathers information to aid in diagnosing issues that can lead to application slowness or deadlocks.
Helps in proactive management by providing alerts on critical thresholds being reached, specifically warning and alarm levels based on the duration a session has been blocked.

Importance

Ensures high availability and performance of database operations by minimizing disruptions caused by blocked sessions.
Alerts database administrators to potential problems before they impact user operations, allowing for quicker resolution and less downtime.
Enhances the ability of database professionals to maintain smooth operational continuity.

Manual Checking

Database administrators can manually check for blocked sessions by executing the following SQL command:

SELECT * FROM master.dbo.sysprocesses WHERE blocked != 0;

Implementation Details

Table Name	Description
dbw_blocked_tab	Temporary storage for current blocked sessions
dbw_blocked_histr_tab	History storage for blocked sessions
dbw_blocking_tab	Temporary storage for sessions that are causing the blocking
dbw_blocking_histr_tab	History storage for sessions that have caused blocking

Procedures Used

The main stored procedure for this task is dbw_blocked_proc. It handles session checking, logging in tables, and alerting.
Supports both warning thresholds (indicating a potential issue) and alarm thresholds (indicating a serious issue needing immediate attention).

Dependencies and Maintenance

Depends on several system objects including master.dbo.sysprocesses and master.dbo.syslockinfo for session and lock information.

Upgrade Notes

Version upgrades include schema checks and adjustments to ensure functionality remains aligned with system and software updates.
Continuous monitoring script ensures all blocked session data is properly logged and aged out to maintain system performance and data relevance.

Reporting Features

The system generates reports showing blocked session details including session ID, the session it is blocked by, wait times, and other crucial diagnostic information.
Reports aim at simplicity and clarity to assist quick decision-making.

Example Report Query for Blocked Sessions:

SELECT t1.spid as "Session ID", t1.blocked as "Blocked by", t2.name as "Database", t1.waittime as "Wait Time" FROM master.dbo.sysprocesses t1 JOIN master.dbo.sysdatabases t2 ON t1.dbid = t2.dbid WHERE t1.blocked != 0 ORDER BY t1.waittime DESC;

Scheduling

Runs at a default schedule of every 5 minutes.
Configurable based on database activity and administrator preferences to either more frequent checks during high traffic periods or less during known low usage times.

Acknowledgements

dbWatch Control Center utilizes a comprehensive approach to monitoring, with a focus on reliability, scalability, and minimal overhead on the target systems.