THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Introducing: Extended Events Code Generator v1.0

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!

Published Thursday, July 30, 2009 4:16 PM by Adam Machanic

Attachment(s): xe_code_generator_v1_000.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

When working with XML in SQL Server, you might want to uniquely identify one node against another. But

August 3, 2009 9:19 AM
 

Adam Machanic said:

If you're one of the estimated 3-5 people who've downloaded and are using my XE Code Generator , please

May 8, 2010 7:34 PM
 

Derek Czarny said:

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.

September 12, 2012 3:13 PM
 

Derek Czarny said:

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'

September 12, 2012 4:34 PM
 

Adam Machanic said:

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.

September 12, 2012 7:49 PM
 

Alex said:

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.

December 19, 2013 2:44 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement