Extended Events at SQL Server Connections with Jonathan Kehayias

I was very interested to hear Jonathan Kehayias’ session on extended events. I know this is one of the areas i need work and it is becoming a more important part of SQL Server with each version. Beginning with a good discussion of what extended events are and the terminology is, Jonathan presented a lot of information. Much like PBM, this subsystem requires you to learn a lot of new names and terms in order to even understand the documentation.

With a dozen or so terms I am sure i wasn’t the only one that was slightly confused, not because of the explanations but just because it’s a lot of information. I know i need to read up on this a bit more before i see another session.

One important note: You Need to learn XML. all of the results come back as xml and some knowledge of xquery is going to be necessary to work with the event results, not my favorite thing, but i have another skill to work on now.

After 20 minutes, it’s demo time. The first questions is do log operations cause waits? We see a demo that shows what is recorded in DMVs, what the events see, and what is reported in the query statistics.

An interesting fact. Once the event data is gathered, you cannot drop the session without losing the data. You can however drop the events and leave the session so you can query the data.

Another item, causality tracking is needed to associate events to a specific process. It adds overhead, but you need this at times to determine which events correspond to which particular sessions doing work.

How often does ghost cleanup run and what does it do? Demo 2 looks at the events that are associated with ghost cleanup. In a busy system, ghost cleanup can become a bottleneck, so it can be handy to know if this is a problem. There is a trace flag to turn this off, but it turns it off for the instance.

The demo looks at what happens when you delete a number of records and the ghost cleanup task begins to fire. By turning off and on ghost cleanup, we can see records being deleted. Lots of events, and we need ghost cleanup hitting lots of records in a short period of time. In fact we saw 12 rows being cleaned up in one ms.

The last demo is a look at checkpoint. What happens with a checkpoint? When does it run during a backup? During the demo we can see the checkpoint being started right after the backup starts and we can see lots of pages being flushed to disk.

Very cool demos. Lots more information than you normally want, but useful stuff for digging into problematic systems.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.