There are a couple ways to handle the locking aspect of this question:
- If you want to know if X seconds of blocking has happened in the last several minutes, DPA includes the Total Blocking Wait Time alert under Wait Time alerts.
- f you want to know if the average amount of blocking for each session has exceeded X seconds, here is a custom alert that should help: Custom Alert - Average Blocking Time per Session
- If you want to know if any session has experienced over X seconds of blocking, you can use the same alert in #2 but use a MAX function vs AVG function in the query.
Deadlocks will occur as soon as the database engine sees the issue, so there is often little time associated with a deadlock. If there is something I am missing about deadlocks, please reply.
Thanks Darichar for your inputs, Will go through and let you know.