Thursday, March 12, 2015

[Extended Events] Dynamically Shredding XML and PIVOTing the results for easy reading

I love Extended Events. I hate XML.  I love the dynamic shred for XML.  I hate having to write XML code when I can have it write itself.

And so....
 Here's an easy(er) way to deal with XE XML.
This XE is specifically to find queries with a duration longer than 1 second.


How, you ask?
We create an Extended Event for items of long-running duration.
We then feed it into a dynamic XML shredder ('local-name(.)'), that kind of thing.
We then take THOSE results and feed it into a Dynamic Pivot.

End result:
no crappy code that looks like:
event_data_XML.value('(event/data[3])[1]','INT') AS object_type,
event_data_XML.value('(event/data[4])[1]','INT') AS cpu,
event_data_XML.value('(event/data[5])[1]','INT') AS duration,


No comments: