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?
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 via
The default value is
0, and while misleading isn't number of seconds, it means
Off; 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:
The blocked process report is done on a best effort basis. There is no guarantee of any real-time or even close to real-time reporting.