Extended Events Code Generator v1.001 - A Quick Fix

Adam Machanic — Sat, 08 May 2010 23:23:00 GMT

If you're one of the estimated 3-5 people who've downloaded and are using my XE Code Generator, please note that version 1.000 has a small bug: text data (such as query text) larger than 8000 bytes is truncated. I've fixed this issue and am pleased to present version 1.001, attached to this post.

Enjoy, and stay tuned for slightly more interesting enhancements!

Introducing: Extended Events Code Generator v1.0

Adam Machanic — Thu, 30 Jul 2009 19:16:00 GMT

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!

Search results matching tags 'Extended Events' and 'code generator'

Extended Events Code Generator v1.001 - A Quick Fix

Introducing: Extended Events Code Generator v1.0