I have enabled deadlock alert shipped with DPA 11.1. It sends me the alert notifications, but does not tell what queries and session ids were in the deadlock, How do enhance it to show the queries and session ids.
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.
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 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?
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.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.