queries and session ids in deadlock alert

To my knowledge, you would have to click on the alert link taking you to the deadlock view in DPA which then would have details involved.

The other option is to create a custom alert returning the data you are looking for.

An example of setting up a custom alert can be found here.

The link takes me to the alerts config page. If I look at this alert history, it does not show me any more details except the deadlock alert time. If I have to create a custom alert for every little thing, then why pay for this product.

These are my personal thoughts as a DBA:

The idea of the alert is to notify you that deadlocks have been occurring in excess of the threshold you choose or configure. The analysis of the deadlocks that are occurring could be quite complex involving more than 2 sessions, different impacts, different deadlock priorities, survivors, victims, etc. Way more info than I personally would want to try to parse through in an email.

DPA was designed to 1. Let you know there is significant deadlock contention, 2. Provide you with a mechanism (in the UI) to investigate overall impact and in a very granular fashion what's causing, who's involved, etc.

Deadlocks auto-resolve, so nothing to do real-time with them. Troubleshooting each occurrence or alert incident would not be my recommended approach to deadlocks.

Happy to hop on a call to look at your environment though and talk through it - perhaps I'm missing something of your requirements or how you approach troubleshooting deadlock contention.

Let me know and I can contact you offline to arrange a screenshare.

The snapshot attached shows what I get when I click on the Details or History for deadlock alerts. That does not help much, other then knowing that a deadlock happened. I need to know what queries were involved in the deadlock, so I can trace them to the job that is running those and schedule those queries at different times. Can I create a custom alert that will give me that information? What tables in DPA Repository database have that information? Please send me a sample query.

Thanks

Since you know the alert fired, you can then go back into trends in DPA and look at the detailed deadlock information.

Click on one of the events and you'll see the queries involved, who survived, who was killed, impact, deadlock priority, etc.

See screenshots below.

Is that what you had in mind?

I don’t see deadlock list and deadlock chart tabs on trends page. How do I get that?

Deep

You'll have to turn deadlock monitoring on:

DPA deadlock collection not enabled by default

And here is a document using DPA to analyze and resolve deadlocks. .

Deadlock analysis in DPA

I set deadlock poll enabled to true.

I still do not see Deadlock tab on the trends page. Will it appear only after a deadlock has occurred?

What are the implications of enabling deadlock poll on the server?

Thanks,

Deep

What version of SQL Server?  Deadlock support is for SQL 2012 and later.   The tab should be there for all compatible version of SQL server.

Ah…we are still at 2008r2, planning to upgrade soon though.