patternsqlMinor
sp_HumanEvents @event_type = N'blocking' and logging job help needed
Viewed 0 times
event_typeloggingneededhelpblockingsp_humaneventsandjob
Problem
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?
#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?
Solution
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
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.
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.
Context
StackExchange Database Administrators Q#299323, answer score: 6
Revisions (0)
No revisions yet.