Extended Event in SQL Server

Introduction

A SQL Server Extended Event is nothing but an event handling system for a server system. In this article, we will learn about SQL Server Extended Event.

SQL Server Extended Event

SQL Server Extended Event are is designed to support correlation among Data, Operating System Data, and Event Tracing for Windows (ETW) data. An Extended Event typically supports using event data outside a process, such as data from 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 tracing; it does not replace earlier methods.

Features of SQL Server Extended Event

Fully configurable event handling Transact-SQL-based mechanism.
Has the ability to monitor active processes dynamically with minimal effect on those processes.
It can be easily integrated with the existing Event Tracing for Windows (ETW) tools. The following are the features of Extended Events:
It 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 so that the configuration of one session does not change the configuration of another session. Still, these boundaries do not prevent an event or target from being 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 and catalog views to obtain session data and metadata. SQL Server 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 errors.

When the SQL Server runs the following query, 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 created by Extended Events in an 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. Its highly scalable and configurable architecture allows us to collect information necessary for troubleshooting the problem.