Extended Event in SQL Server

Introduction

A SQL Server Extended Event is nothing but an event handling system for a server system. Extended Events are designed to support correlation among Data, Operating System Data and Event Tracing for Windows (ETW) data. Typically an Extended Event supports using event data outside a process such as data used by a tracing and logging tool.

An Extended Event in SQL Server provides a generic tracing and troubleshooting platform that allows us to trace deeply. That was not provided by earlier methods, like DBCC, Profiler etcetera. Note that an Extended Event is an alternative way of do the tracing; it does not replace earlier methods.
 
Features

The following are the features of Extended Events:

• fully configurable event handling Transact-SQL based mechanism.
• has the ability to monitor active processes dynamically with minimal effect on those processes.
• can be easily integrated with the existing Event Tracing for Windows (ETW) tools.
• is an integrated approach to handling events across the server, enabling users to isolate specific events for troubleshooting.

Some Important Terms

The following explains the terms Extended Event Packages, Extended Event Target, Extended Events Engine  and Extended Event Session.
 
Extended Event Packages

A package is the container of the Extended Event Object.  There are three types of Extended Event packages; they are:

• Package0: this is the default package
• SQL Server: SQL Server related object
• Sqlos: SQL Server Operating System related object

A package can contain any or all of the objects, like Events, Targets, Actions, Types, Predicates and maps.

Extended Event Target

A Target is an event consumer. A Target may be an output file, event data or event related task. It can process the data synchronously or asynchronously.
 
Extended Events Engine

An Extended Events Engine is a collection of services and objects. It does not provide events or actions to take when the event is fired.
 
Extended Event Session

An Event Session is created within SQL Server process hosting. Extended Event sessions have implied boundaries in such a way that the configuration of one session does not change the configuration of another session, but these boundaries do not prevent an event or target used by another session.
 
An Extended Event provides a Data Definition Language (DDL) statement for creating and modifying an Extended Events session. There are dynamic management views and catalog views to obtaine session data and metadata. SQL Server 2012 is also provides a Graphical UI for managing event sessions.
 
Example

The  following is a "Hello Word" example.

Step 1: Create a session and add an event and target within the session:

CREATE EVENT SESSION error_test_session ON SERVER

ADD EVENT sqlserver.error_reported

 

ADD TARGET package0.asynchronous_file_target

(set filename = '\\localhost\data1.xel' , metadatafile = '\\ localhost \data1.xem')

Note: Please replace the file path with localhost.
 
Step 2: Start the sessions:

ALTER EVENT SESSION error_test_session ON SERVER STATE = START
 
Step 3: Test and trace error:

When the following query is run by SQL Server, it throws a cast conversation error.

DECLARE @data VARCHAR(50)

DECLARE @intData INT =10

SET @data = @intData + 'here I am trying to concate int to varchar'

 
The "sys.fn_xe_file_target_read_file" function reads a file that was created by Extended Events in a XML format.

 

SELECT CAST(event_data AS XML) AS event_data FROM sys.fn_xe_file_target_read_file

('\\localHost\data1*.xel' , '\\localHost\data1*.xem',null,null)

XML Output of error

<event name="error_reported" package="sqlserver" id="100" version="1" timestamp="2013-05-14T08:21:28.763Z">

  <data name="error">

    <value>245</value>

    <text />

  </data>

  <data name="severity">

    <value>16</value>

    <text />

  </data>

  <data name="state">

    <value>1</value>

    <text />

  </data>

  <data name="user_defined">

    <value>false</value>

    <text />

  </data>

  <data name="message">

    <value>Conversion failed when converting the varchar value 'here I am trying to concate int to varchar' to data type int.</value>

    <text />

  </data>

</event>
 
In another example with XML data, we can also throw an error using the RAISERROR function in SQL Server; see:

 

RAISERROR (N'This My Test Error to check Extended Event', 2, 1, 7, 3, N'Test');

<event name="error_reported" package="sqlserver" id="100" version="1" timestamp="2013-05-14T08:36:12.348Z">

  <data name="error">

    <value>50000</value>

    <text />

  </data>

  <data name="severity">

    <value>2</value>

    <text />

  </data>

  <data name="state">

    <value>1</value>

    <text />

  </data>

  <data name="user_defined">

    <value>true</value>

    <text />

  </data>

  <data name="message">

    <value>This My Test Error to check Extended Event</value>

    <text />

  </data>

</event>
 
Step 4: Stop and drop the event Session
 

ALTER EVENT SESSION error_test_session ON SERVER

STATE = STOP

DROP EVENT SESSION error_test_session ON SERVER
 
Conclusion

An Extended Event is a lightweight performance monitoring system that uses fewer resources to trace the task. It has a highly scalable and configurable architecture that allows us to collect information necessary for troubleshooting the problem.