SQL Server 2008 Extended Events. It’s a high-performance, feature-rich, and extremely useful tracing system. Too bad it’s so difficult to figure out how to use it! Or is it?
Earlier this week Jonathan Kehayias announced his SSMS addin for Extended Events, which makes it super-easy to create and manage events, and even view the raw event data. It’s all done via a simple UI, so if you don’t have time to learn how to do all of that stuff with the Extended Events DML and DMVs, no worries.
But what if you have your event all set up thanks to Jonathan’s addin, and you find that simply viewing the data isn’t enough–now you want to query it, and actually do some analysis. Crazy idea, isn’t it? Now you’re stuck, unless you want to learn how to do that XQuery stuff. Or are you?
Extended Events Code Generator is my solution to this second problem. Here’s how it works: You install the Code Generator, which is simply a T-SQL UDF (with an optional SQLCLR component–more on that later). Then you set up your Event Session(s), with or without the help of Jonathan’s addin. And when you’re ready to query, you ask the Code Generator to write some code for you. It sets you up with a fully-pivoted view of the data, with all of the columns cast to the appropriate T-SQL data types. Now all you have to do is code. And you’ll even get the benefit of full Intellisense support, because you’re just dealing with columns in a derived table, not XML.
In addition to making querying a breeze, the generated code will also make a lot of Extended Events queries faster. I sat down with Michael Rys–Mr. SQLXML himself–at TechEd, and he was kind enough to spend a few hours helping me tune the generated code to make it as fast as it can possibly be. And while that’s relatively fast (much faster, thanks to Michael’s help, than my previous attempts), I wanted it to go even faster, so I created a SQLCLR helper function, included in the attached script, that improves performance by a factor of 3-5 on top of what Michael helped me achieve.
Attached to this post you will find the XE Code Generator, and at the bottom of the script–commented out for those of you who are too paranoid to run SQLCLR code–is the optional helper function. Install the Code Generator in a database of your choice (I recommend tempdb), and if you feel like it and will be using asynchronous file targets, install the SQLCLR function too. The Code Generator will automatically generate code to take advantage of the SQLCLR function if it’s installed, or will generate native SQLXML code otherwise. The choice is yours.
Set up an Extended Events session or two, capture some data, and go crazy. I think you’ll find that it’s not only quite powerful but also a lot of fun to work with. So much fun, in fact, that I’m doing a session on the topic at the PASS conference this November, where I’ll share many of the interesting use cases I’ve found for it to date. And of course all of the examples will use code generated by the Code Generator.
Feel free to reply here with questions, comments, or feature requests. And as always, enjoy!
File Attachment: xe_code_generator_v1_000.zip
Thanks Adam, I was just trying to learn Extended Events by going through Jonathan’s articles and began to write my own, when I jumped to the end of Jonathan’s articles and found your function. That was exactly what I was trying to do.
I liked some of the examples on the deadlock extended events, where you can click on the xml output in the results and it opens in another query window. On line 309 in your code generator is
WHEN column_name = ‘tsql_stack’ THEN ‘XML’
I modified it to get that same effect:
WHEN column_name in (‘tsql_stack’,’xml_report’) THEN ‘XML’
Thanks, Derek. Are you aware of the newer version?
http://sqlblog.com/blogs/adam_machanic/archive/2010/05/08/extended-events-code-generator-v1-001-a-quick-fix.aspx
Unfortunately I don’t think either version works (well?) with SQL Server 2012. I need to revisit, because I made some assumptions that are no longer true about how the XML output would look.
Adam-
Excellent piece of coding! I found another issue with this code when using EEs to monitor Service Broker.
The event
broker_remote_message_acknowledgement.priority
is scoped as int8 in the EE metadata which translates to TINYINT in your code on line 303. This event can contain negative numbers.
Therein lies a problem, TINYINT is unsigned and has a range 0 to 255 BUT INT8 is signed and has a range -128 to 127 (assuming that INT8 is a Windows Data Type).
Suggest changing line 303 in your code from:
WHEN ”int8” THEN ”TINYINT”
to:
WHEN ”int8” THEN ”SMALLINT”
That change worked for me.
Hi Adam, just downloaded this very useful code. Looking at the xquery I’m curious about the optimisations you implemented – is there a before / after?
@James:
There probably was at some point, but it’s six years later and I don’t have it archived. Sorry!
–Adam