Use extended events for deadlock "tracing" instead of a trace flag
If the SQL Server version is high enough, using extended events to capture deadlock information would be preferred to using a trace flag.
SQL Monitor now supports extended events.
We have had a further suggestion to enable this per-server rather than globally, which has been added as: https://sqlmonitor.uservoice.com/forums/91743-suggestions/suggestions/31754800-enable-extended-events-per-server
SQL Monitor Development Team
Please make this be enabled per-server instead of globally. We have SQL 2008 servers which prevent our use of this feature due to well-documented issues (https://support.microsoft.com/en-us/help/2878139/fix-using-dbghelp-dll-version-4-0-5-error-is-logged-in-sql-server-2012)
Blaž Dakskobler commented
A question: does enabling this also turn off TF 1222 on monitored instances?
János Berke commented
This TF is the most annoying since the default system health XE as well as event notification can capture the deadlock graphs. I am happy to share my event notification based solution instead of TF.
Mathew Walters commented
It doesn't really make sense using trace flags for deadlock detection since extended events has been around. Using the system health session is good because it is already built into the product. I would also like the option of having SQL Monitor create a separate deadlock XE, this is because I have worked with a few servers where the system_health XE gets flooded with other notifications so it gets cycled every few minutes.
Andrew Lackenby commented
Display the transactions involved in the dead lock in a graphical manner
select target_data as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health'
Gives nice XML data on my server, can't you use that?
Do you mean that you've not getting anything useful in the 'Details' tab of a Deadlock alert? If that's the case, please could you drop an email to firstname.lastname@example.org (please mention me and this UserVoice thread) with the content from the 'Output' tab, making sure to retain the indentation, so we can take a look at this.
Morten Nilsen commented
deadlock parsing is still broken for me with SQL Monitor 22.214.171.1246
Thanks for the suggestion, Can you explain further as to what you would hope to achieve by exporting to an external tool?
FYI, the output shown is exactly as presented to us by SQL Server, which somewhat unhelpfully strips out the XML tags. :o(
Export deadlock xml from output tab to view in external tool.
The view presented under the output tab is difficult to read and its an xml representation.
Alternatively just show the xml instead of the formatted text.
Add user and application to the details tab on a deadlock alert.
And to the email notification.
Thanks a lot for your feedback.
Hi Priya... It will still be much easier and more simple if we can get an "disable usage of trace flag 1204" button. Especially because we have to set specific permissions on ALL databases we want to monitor, if we want to avoid this trace flag. – This can of course be scripted etc. But the disable 1204 button, will just make it much more simple and easy.
Kenneth L commented
For Deadlock alerts involving indexes/keys under the Details tab: the Lock details erroneously represent the hobt_id and index hash key as object id and index id respectively. Trying to SELECT OBJECT_NAME([object id]) will typically result in an overflow error, especially because the value being represented is the hobt_id. The hobt_id corresponds to the hobt_id column in sys.partitions from which the object_id can be derived.
In the attached example, 72057594042974208 is the hobt_id and (5d01b1d6d489) is the index hash key... NOT the object_id and index_id values.
Robbie Baxter commented
Like you said Priya, if this could be done with little overhead, it could/would be very helpful.
Thanks Jonathan and Tim for the feedback. We will definitely review this and let you know our findings. Sometimes information can be extracted but it comes with a cost.
We try really hard to keep SQL Monitor light weight.. So that you should never notice any performance impact on the entities you are monitoring. Will update you when we have investigated this.
Jonathan Boarman commented
Oh, that is pretty cool. Thanks Tim! Your idea definitely gets my vote.
Having a call stack certainly helps increase visibility on the context where a particular issue is arising. And since SQL Deadlock Detector can do it, I don't see why SQL Monitor couldn't do it as well.
Tim Garcia commented
The screenshot is from another product, "SQL Deadlock Detector" from Lakeside Solutions. I meant to mention that in the original post for the new idea, in order to show that it's apparently possible to get that call stack information *somehow* since another product does it. :)
Jonathan Boarman commented
Where is screenshot from? I don't recognize it.
Tim Garcia commented
In the display of alert detail, rather than just showing the SQL fragment, show the full call stack. This helps to determine the context in which the fragment is running, since often the fragment alone is a piece of shared T-SQL code (like a UDF) that can be called by several vastly different pieces of code.