3

Those who use excellent sp_HumanEvents, maybe the author himself, please help me understand something I am missing (cause of my stupidity).

#1

When monitoring blocking, blocked process threshold must be set (in seconds), otherwise the blocked_process_event would not be fired. How this correlates with @blocking_duration_ms parameter?

Example: blocked process threshold id set to 10 seconds @blocking_duration_ms is left default = 500 ms

#2

When I want to log results into the table continuously and independently on the server restarts I am advised to use a Agent Job with an example that sets a schedule with a name sp_HumanEvents: 10 second Check In but runs recurringly on Sundays midnight. Wouldn't be starting it automatically when SQL Server Agent starts more appropriate?

6

First

The blocked process report seconds is how many seconds blocking has to go on for before it makes it into the report. I don't have a specific recommendation here, but 10 or higher is usually a good choice.

The parameter for sp_HumanEvents is the threshold for what gets shown to you or logged to a table. You are free to set either value to what makes sense based on local factors. It's probably set artificially low as a default because it made it easier for me to test during development.

Second

Presumably you're talking about the Agent Job example from the repository, which is just that: an example. You're free to modify it to whatever meets your needs. I believe I set it up that way to check in case something happened to a session between restarts.

2
  • ad 1 Am I right to say that @blocking_duration_ms should be higher than blocked process threshold if I want to log processes being blocked longer than a threshold and when it is lower the threshold is the given limit?
    – jericzech
    Sep 9 at 14:11
  • @jericzech right, but at that point you might as well set the blocked process report threshold to whatever you're interested in and just like the session get all of that. There's not a lot of sense in collecting blocking data that you won't log or review. Sep 9 at 14:25

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.