When it comes to blocking, DPA can currently alert when the cumulative blocking time goes beyond a threshold. The alert can be found under the category of Wait Time and is named Total Blocking Wait Time. However, one drawback to alerting on cumulative blocking time, is that it can potentially be noisy in a busy server. For example, lets take 2 blocking scenarios:
- 10 root blocker sessions each blocking 20 other sessions for 15 seconds
- 1 root blocker session blocking 2 other sessions for 150 seconds
The base DPA alert would calculate this as 300 cumulative seconds of blocking wait time in each case. Depending on your environment, both could potentially be bad, but I would probably want an alert for the second case and not the first. How can we tell DPA to alert differently.
Custom Alerts in DPA allow you to extend the capability of the alerting feature. If you can write a SQL statement to do what you want, that SQL can be plugged into DPA. In my case, rather than alert on cumulative blocking wait time, I want to alert if a session has been blocking for more than 3 minutes of clock time. For this, we can use a query similar to this:
SELECT blocking_session_id, MAX(wait_duration_ms)/1000 blocking_secondsFROM sys.dm_os_waiting_tasksWHERE blocking_session_id IS NOT NULLGROUP BY blocking_session_id
We can configure a custom alert in DPA to execute this once a minute to perform the check. Attached is a screenshot of the alert and when creating it, choose Custom SQL Alert - Multiple Numeric Return. In my example, I set thresholds for warning and critical at 90 and 180 seconds, but adjust accordingly for your requirements.
