I have tons of BPR in .xel file (XE) and I want to be able to view them in a graph form and dump them all in SQL Server table, is there any way to do so?
Instead of the 5 sec threshold is there any way I can see the reports coming in LIVE?
Sql-server – Blocked Process Report
blockingsql server
Related Question
- Sql-server – Blocked Process Report SQL Server 31 hour duration
- Sql-server – How many deadlocks am i really getting? system health or deadlocks/sec
- Sql-server – Indexed view in SQL Server
- Sql-server – Empty blocking process in blocked process report
- Sql-server – Block SSRS Developers from embedding SQL Statements in Report
- Sql-server – the right tool to process big .xel files (SQL Server Extended Events logs)
- Sql-server – Edition of Power BI Report Server
- Sql-server – How to query an Extended Events target file on a SQL Azure Managed Instance by using T-SQL
Best Answer
This is only a partial answer as your requirements for viewing the reports in graph form are a bit vague for me, but that could also be because I tend not to aggregate blocking reports; instead I enable their collection on an as-needed basis.
This answer addresses the 5 second threshold. As already pointed out by @Aaron Bertrand in the comments, blocking is a natural and expected behavior of SQL Server. You can adjust the duration that blocking needs to occur before a query shows up in the Extended Event blocking report by adjusting the
blocked process threshold (s)value viasp_configure.The default value is
0, and while misleading isn't number of seconds, it meansOff; all values greater than 0 refer to seconds. The minimum number of seconds that blocking is required before queries will show up in the report is 1 second per my experience. Also, take note that there are no guarantees when collecting blocked processes via the blocked process report. As noted in the aforementioned article: