Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
khermann
Starting Member
10 Posts |
 Posted - 2014-10-22 : 12:14:09
|
| We have team members who are at odds over how this event is handled and it's coverage.After enabling this is sp_configure, and for the sake of argument keeping the threshold = 5 (default), and no other trace filters are applied - which of the following is true, false, or misunderstood:1. All-inclusive. The report will be generated for ANY transaction that is blocked for more than 5 seconds, meaning all transactions are contrasted against this threshold and no such transactions would be missed.2. "ad-hoc query-like" ; point-in-time only. The threshold is only contrasted against at a point-in-time only, in that at a set interval active transactions are reported against. This would mean blocked transactions that occur between this interval would not be reported against. Reading the MSDN articles, the language used is terse and somewhat left open to interpretation.The general discussion that started this is around whether or not running an external query (such as in SSMS) to report on blocking (which only reports on transactions at that point in time) is basically the same thing as running the blocked process report trace event, except in the trace event the "query" is run at "some interval" automatically.I am pretty sure I will win a beer if I am right on my answer to this, so I will not color the results with which answer is "mine" - but I need expert arbitration. Thanks very much for the assist!Edited: typos |
|
|
khermann
Starting Member
10 Posts |
Posted - 2014-10-27 : 12:41:50
|
| Nothing?I can't tell if that means my question is lame/stupid or too difficult to answer. I guess it doesn't really matter from a practical perspective :) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-10-27 : 17:05:06
|
| The Blocked Process Report event in Profiler is constantly running once it's configured.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Clarification on Blocked Process Report