Power Tools: Instruction Manual

Just like any good tool man, whenever you get a new tool 

The first thing you should do is to read the instruction manual and familiarize yourself with the general operation of the tool.

During this first installment, we will do just that.

As mentioned we are going to dive into the components that make up Extended Events.

First, what are Extended Events?

Extended Events (XEvents) has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem. For the .NET programmer you can think of extended events as analogous to Trace statements.

As various parts of the SQL Server executable code gets executed, the execution passes through an extended event definition. If no one is listening, it is essentially a no-op. If there is a listener, the requested information is gathered and made available to the engine for consumption.

Extended Events are based on Event Tracing for Windows (ETW). In a future version of SQL Server, SQL Tracing / Profiling will be deprecated and Extended Events will be your only SQL Server collection tool for troubleshooting and performance tuning.

There are two default Event Sessions (more on sessions later) defined by SQL Server

1. System Health - for more information please visit the CSS Engineer blog describing the  System Health session.

2. Deadlock detection - yes, you will now have deadlock information available even if you have not enabled the deadlock trace flags or defined a SQL Trace with Deadlock Events.

 What makes up the Extended Events System?

There are three system components defined by the SQL Server Product Group, and one that you the DBA will define (per collection actually).

 

 

 

Packages

Packages are defined by the SQL Server team and are containers for the various objects that make up the sets of data that can be collected.

In SQL Server 2008 there were three public (and one private) packages defined. The SecAudit package is the private package

 

In SQL Server 2012, the product group added four new packages and a secondary definition of an existing package. An interesting technique was used to allow the product group to extend the originally packages.

Notice below that there are two entries for sqlserver. However, since we always refer to the packages by name rather than by GUID, the new objects defined in the SQL Server 2012 sqlserver package are available to us along with the objects originally defined in the sqlserver package.

 

As we go through this series we will examine many of these packages, detailing when they might be useful and how to effectively use them.

Packages are defined in modules (a module being either a specific dll or an executable such as sqlserver.exe). Packages contain many different object definitions, which we will discuss in future posts.

Perhaps this picture will help...

We will examine most of these objects in the course of this series. Just remember, that the package is where these objects are defined by the product group.

 

Targets

 So, where packages are object definitions, targets are event consumers.

• Targets can write to a file, aggregate event data, or start a task that is related to the event.
• Targets can process data synchronously or asynchronously.
• Targets can be either file-based or in memory

If we examine the system dynamic management views, we can see what targets are defined and whether or not they are available for our use (any target with a private capabilites description is not available to us).

 

In a future post, we will discuss the various types of targets and how they are used to consume events.

Engine

The SQL Server Extended Events engine is a collection of services and objects that:

•Enable the definition of events.

•Enable processing event data.

•Manage Extended Events services and objects in the system.

•Maintain a list of Extended Events sessions and manage access to that list.

 

 

Sessions

 As this is the user defined portion of the Extended Events system, we will be devoting many posts to this topic.

 

That's it for now... Next week we will start drilling into each of these components.