Using Blocking Event Collector Job (Extended Events) functionality

Prerequisites

Note: To use this functionality, you must first install the dbWatch job named Blocking Event Collector.
This job is part of the SQL Performance Package license.

The Blocking Event Collector Job is designed to capture and persist information about SQL Server blocking situations. It leverages SQL Server’s built-in Blocked Process Report event through Extended Events to provide detailed insight into blocking chains and affected sessions.

This job enables historical analysis of blocking incidents, helping database administrators identify performance bottlenecks and troubleshoot concurrency issues.

Event Capture

For each detected blocking event, the job captures:

The blocked session
The blocking session

This ensures full visibility into the blocking relationship.

Collected Data

For every blocking event, the following session context is stored:

Session Information:

Session ID (SPID)
Database name
Login name
Host name
Application name

Query Details:

SQL statement text
SQL handle (when available)
Statement start and end offsets (when available)

Execution Plans:

Query execution plan (when retrievable)
Support for multiple query plans per blocking event
Ability to correlate plans with both blocked and blocking sessions

Additional Insights

Post-Analysis Capabilities

The collected SQL handles and offsets can be used to retrieve:

Execution plans
Query statistics
Additional metadata from Dynamic Management Views (DMVs)

This allows deeper analysis after the blocking event has occurred, especially in cases where multiple queries contribute to the blocking chain.

Benefits

Key Advantages

Historical visibility into blocking issues
Identification of problematic queries and sessions
Access to execution plans for deeper performance analysis
Support for multiple query plans per blocking event
Lightweight monitoring using Extended Events

Requirements

SQL Server with Extended Events support

Conclusion

The Blocking Event Collector Job is an important component for proactive performance monitoring in dbWatch. By capturing detailed blocking data, including execution plans and multiple query plans per event, it enables efficient troubleshooting and root cause analysis of complex blocking scenarios.