Extended Events to find Blocking and Deadlocking

Question

Came across another great article from Jeremiah Peschka called Finding Blocked Processes and Deadlocks using SQL Server Extended Events and set this up on my local MS SQL 2014 Developer edition and created blocking and deadlocking events and was able to run his provided query which generate XML for both events.

I took the same process up to a production server MS SQL 2012 Enterprise edition and although I can query some captured deadlock events, the report column is blank, contains no XML for any captured event. Thoughts?

Also, I am currently running the production server with deadlock trace flags -T1204 & -T1222 and getting deadlocks logged to the SQL log but although this extended event query generated many rows of REPORT TYPE=Deadlock, no corresponding deadlock was written to the SQL Log, at least around the same time, I have many many more at other times in the SQL log, hence my work in this environment. What am I missing?

If I open the .xel in SSMS I get a column with the XML string and can double click to have it parse/format and display it in a new window. I'm just not sure why the hyperlink doesn't display in the original query.

Answer 1

Glad you liked the post - it was by Jeremiah Peschka though, not me. (I know because I use it all the time myself!)

Make sure to read the entire article, though, which includes the answer you seek:

Extended Events deadlock graphs use a slightly different XML schema than what SSMS expects. You should see an error along the lines of “There is an error in XML document”. For folks using SQL Server 2012 and earlier, you can either parse the XML by hand or use SQL Sentry Plan Explorer.