Extended Events for Anything But Query Tuning: xml_deadlock_report_filtered

One of my favorite little bits of information about Extended Events is the fact that everyone running a full instance of SQL Server has deadlock information available to them, even if they never enabled Trace Flag 1222 through the system_health session. That captures the xml_deadlock_report which has the full deadlock graph. However, what if you want to capture deadlock info, but, you’re dealing the GDPR, and transmitting query values could be problematic? Enter xml_deadlock_report_filtered.

xml_deadlock_report_filtered

If you do a search for this event, you’re not going to find much. Doesn’t seem like anyone, including Microsoft, has bothered to document it. This is not going to be a comprehensive definition for all things xml_deadlock_report_filtered. However, I can show you why you might want to use it.

This is a port of the standard deadlock graph (using pictures because the full XML is huge and I don’t want to make this post nothing but XML):

xml_deadlock_report

All the standard stuff. Session id of the deadlock victim. Wait resource type and value. Input buffer. All the stuff that describes what happened within the deadlock.

Here’s the deadlock graph from xml_deadlock_report_filtered:

xml_deadlock_report_filtered

The difference should leap into your eyeballs. Where is the input buffer? It simply says “filtered”. Where is the execution stack? It just says “filtered” too. Basically, query data that can easily have GDPR stuff in it is filtered out.

Now, I hear you screaming, “But Grant, how the heck do we fix a deadlock if we don’t know which query it’s from”. In fact, I know that one of the issues that people frequently hit is that they have all the data, but they can’t correlate it a specific procedure, batch, or prepared statement. Take away the T-SQL and that gets even harder. You’re right.

However, what if you want to capture the event, and details about it, to share with other teams, maybe ones that aren’t supposed to see the protected information in the query? Here you go.

Plus, note that you still have the sqlhandle. You could go to the DMVs to query the cache to see if you can identify the query in question, if you need to.

Conclusion

I am absolutely NOT saying xml_deadlock_report_filtered replaces the full deadlock graph. In fact, I would seldom use this in place of the deadlock graph. What I am saying is, this is a nice to have. It does open up your options for how you can capture and share deadlock information.

I hope you’re enjoying these little bits of “XE for Anything But Query Tuning“. I figured I’d try a few out to see if it shows how helpful Extended Events can be. The fact of the matter is, they do so much more.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.