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 when triggered saves 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 the right table shows the deadlocks statements.
The deadlocks table shows the input statement as well as the statement from the first frame in the deadlock extended event.
In both tables the column “Data set” can be used to group the rows that are from the same extended event.
When right-clicking in the 2 tables you go to the details pages showing all the data available in the Power BI report.
Blocks details – drill-through page
The Blocks details page shows the details available in the Power BI report about the blocking and blocked statements.
As the extended event for blocked statements contains a lot more of information than presented in the report you can execute the query shown in the info block at the bottom of the page against the FHSQLMonitor database.
The query statement can be copied to the clipboard by right-clicking in the cell and select “Copy value”.
In this example the query is:
SELECT * FROM [FHSQLMonitorDemo].dbo.fhsmInfoBlocks WHERE Id = 111;
When clicking on the XML hyperlink in the BlockXML column the XML document is shown.
Deadlock details – drill-through page
The Deadlock details page shows the details available in the Power BI report about the statements involved in the deadlock.
As the extended event for deadlock statements contains a lot more of information than presented in the report you can execute the query shown in the info block at the bottom of the page against the FHSQLMonitor database.
The query statement can be copied to the clipboard by right-clicking in the cell and select “Copy value”.
In this example the query is:
SELECT * FROM [FHSQLMonitorDemo].dbo.fhsmInfoDeadlocks WHERE Id = 102;
When clicking on the XML hyperlink in the DeadlockXML column the XML document is shown.
As part of the deadlock XML document the data for the deadlock graph is also available, and to help use it the graph document has been extracted into the DeadlockGraph column.
When clicking on the XML hyperlink in the DeadlockGraph column the XML document is shown as text.
To see the deadlock graph graphically save the XML document as a XDL file.
After saving the document using the file extension XDL, then reopen the file in SSMS and the deadlock graph is now shown graphical.
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.
