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?

  • 2
    You want to see reports of every block as soon as it starts? Blocking is a pretty standard behavior in a transactional/relational database, and you want blocking. It's only long-running blocking that might indicate issues. The threshold is there (a) to keep you focused on problematic blocking and (b) to not overwhelm you with mountains of data describing every single block that ever happens. Commented May 24, 2019 at 15:45

1 Answer 1


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 sp_configure.

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.

  • Also worth noting that SQL Server docs suggest you don't set it to less than 5 (but 0 is ok since that's OFF). Setting it to below 5 can cause issues. Sorry, I don't have a reference for this right now.
    – Rory
    Commented Sep 29, 2020 at 13:46
  • "Values 1 to 4 should only be used for troubleshooting, and never long term or in a production environment without the assistance of Microsoft Customer Service and Support." learn.microsoft.com/en-us/sql/relational-databases/…
    – Rory
    Commented Sep 29, 2020 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.