0

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. – Aaron Bertrand May 24 at 15:45
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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