New Monitor request: "Cumulative Blocking Session Wait Time"

by Mar 27, 2019

Our current "Blocking Session Wait Time" alert trigger is 1,800 seconds for a particular host (business unit's choice).  Recently we had had a long-running query that was blocking other sessions, but were unaware as none of the sessions were blocked long enough to trip the limit–each of the blocked sessions were aborted (either the user cancelled the request or the application timed out and killed the blocked session).   We only became aware when we were notified that users were getting timeouts in the application.

The business unit is hesitant to reduce that limit, but wanted to know if we could trigger based on "cumulative" time… Basically, if possible, we'd like to have a monitor for which we can trigger an alert if the session has blocked 1,800 seconds worth of other sessions during the blocking session's lifetime.

I know this might be a difficult monitor to implement since it isn't dependent on just viewing the current blocking details, but could be a very helpful feature.