SQLskills Training, Day 3: Hello Extended Events!

It’s the end of day 3, and I admit, I didn’t even get this post started this morning…but that certainly isn’t because I wasn’t looking forward to today.  Most of today was presented by Jonathan ( t ), and we started with Extended Events.  I am glad we did this first!

I took a lot of notes and I asked a lot of questions.  Extended Events is hard to wrap your head around until you see it, but in order to understand what you’re seeing, you have to talk about things first.  It’s a chicken-and-egg thing, and Jonathan kept telling us it would all make sense when we got to the demos.  He was right, though it didn’t hurt that he was the most animated I’ve seen him when he was running through the demos and talking about Extended Events.  Paul covered Resource Governor in the afternoon, and then Jonathan finished with Benchmarking and Baselining.  Resource Governor is pretty straight forward, and I have a presentation on Baselining for the Summit, so I wanted to write a bit about Extended Events.

Disclaimer: If you want any resources for Extended Events, I recommend you head over to Jonathan’s blog.  I lost count of how many times he said today, “I did a blog post on that.”

Components of Extended Events

Target – this is where the data I capture with Extended Events is written.  The query below will give you all the target options, but two examples for target are file and ring buffer. 

SELECT name, description

FROM sys.dm_xe_objects

WHERE object_type = 'target'

ORDER BY name

I can choose to write the data you want to collect directly to a file, and then query that data using T-SQL (there is no API to read the file).  Or, I can write the data to the ring buffer (located in memory) and then read it from there.  Be aware that when you write the data to the ring buffer, if you don’t take that data and persist it somewhere (e.g. to a table) you can lose it when you drop your session.

Events – these are the specific actions I want to capture information about.  You can run the query below to see the different events, but some examples include the completion of a T-SQL statement, a page split or a wait.

SELECT name, description

FROM sys.dm_xe_objects

WHERE object_type = 'event'

ORDER BY name

Predicates – these are filters.  If I want to capture information about waits, I can filter to only look at waits that last for more than one second.  If I want to capture information about T-SQL statements that have completed, I may want to only see statements that require more than 10,000 reads.

Event Session – this is what actually gets created and executed based on what I define for Events, Predicates, Target, etc.  I can create an Event Session, start it, stop it, alter it by adding or removing Events or Targets, and drop it.

I am not going to discuss packages, actions, types, maps, dispatching  or customizable columns today.  There is a lot to Extended Events, but they are really, really cool.  Two things Jonathan showed us today that have immense value to me are using Event Bucketizer as a Target and the ability to track causality.

When you specify the Event Bucketizer as a Target, it allows you to collect information about how frequently an event fires based on certain criteria.  For example, if you use Performance Monitor to track Page Split/secs, you have no idea where they’re occurring in terms of database or object.  With Extended Events, I can capture the Page Splits and group them by database.  Think of it as a bucket for each database, and every time a Page Split occurs for a database, the value for that bucket will increment.  After I figure out which database has the most Page Splits, I could run a similar session but add database as my predicate (so I’m only looking at one) and have the objects as buckets, so I can see what objects have the most Page Splits.

Extended Events also includes the ability to only capture an event when a value for that event is higher than it was for any previous execution.  For example, let’s say I have WRITE_LOG waits, and the following occurs:

If I were capturing all waits that lasted more than one second, I would capture rows 2, 4 and 5 in my session.

If I were capturing only the waits lasted longer than any previous execution, I would capture row 1, then row 4, but row 5 would not be captured (because 1005 is not higher than 1300). 

As I’m going through the notes, I realize there are a lot more things I could write about.  Yeah…I learned a lot about Extended Events today.

Other random items of note:

• “Benchmark special” is not something we have for lunch
• I finally saw Building 35 on the Microsoft campus, thanks to Jimmy May ( b | t ) and his lovely bride.
• I learned that there is a Top Pot just a few blocks from the hotel.  I can’t believe it took me three days to discover this.  Guess where I’m going tomorrow morning?