This blog is part of the blog series named SQL Server Monitoring.
Purpose
To show the detected blocked queries and deadlocks based on the data collected by extended events.
Sources
This feature uses the extended events sqlserver.blocked_process_report and sqlserver.xml_deadlock_report which triggered shaves the data into XEL files.
When the service executes it reads new records in the XEL files and extract the data and save it into a table in the monitoring database.
Report
The report is divided into 2 parts.
The upper part is showing 2 stacked column charts, where the left chart shows blocks over time, and the right chart deadlocks over time. Both charts categorizes the data by client application.
The lower part shows 2 details tables, where the left table shows the blocks collected, and where both if the blocked statement as well as the blocking statement are shown. The right table shows the deadlocks statements and where the column “Data set” in the table can be used to group the deadlocks rows that are from the same extended event.
Model
The model for the report uses the facts [Blocked process] and [Deadlock], and the dimensions [Date], [Time], [Blocks and deadlocks] and [Database].
Configuration
The feature accepts a FilePath parameter to specify the directory where the extended event files should be saved. If not configured or blank it service uses the SQL Server LOG directory.
In order for the extended event sqlserver.blocked_process_report to work, the blocked process threshold must be configured.
The blocked process threshold article shows how to configure the option, and the example uses 20 seconds as threshold for when to consider a process has been blocked.
Scheduling
The default schedule for the feature is every 5 minutes (300 seconds).
Retention
The default retention for the tables dbo.fhsmBlocksAndDeadlocks is 7 days.
