SQL Server Extended Events

January 25, 2017

Mahammadrafik

What are the SQL Server Extended Events?

SQL Server Extended Events 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.

The impact of collecting the data on SQL server will be minimal compared other available tools/components.

The History

The extended events existed in SQL Server 2000 in perf monitor tool as Event Trace Sessions under data collection sets. It was introduced in SQL Server 2008 and more XEvents were added in higher SQL Server versions.

Evolution

The extended events started with SQL Server 2008 and growth of XEvents given below

Extended Events Components

Packages

A package is a container for SQL Server Extended Events objects. There are three kinds of Extended Events packages, which include the following:

• package0 – Extended Events system objects. This is the default package.

• sqlserver – SQL Server related objects.

• sqlos – SQL Server Operating System (SQLOS) related objects.

Events

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.

Predicates

Extended Events introduces a much better system of filtering using Predicates that are applied at the individual Event level, allow for short-circuiting of evaluation, and provide the ability to create complex groups of independent criteria, ensuring only Events of interest are captured by the Event Session.

Actions

Additional information about the event

Targets

SQL Server Extended Events Targets are event consumers. Targets can write to a file, store event data in a memory buffer, or aggregate event data. Targets can process data synchronously or asynchronously.

The Extended Events design ensures that targets are guaranteed to receive events once and only once per session.

Types

Data types for event columns, actions or global predicate sources

Maps

Lookup for specific values (i.e. WaitType99 = “Network_IO“)

Event Session

Collection of Events, Actions, Predicates, Targets

Where can we use extended events

• Almost everything can be accomplished similar to SQL Trace & Profiler

• Deadlock-analysis

• Using system-health (totally for free even historical data) !

• Find out lost connections (queries that did not end)

• Get Waits and Latches of specific queries/procedures

• Trace problematic page splits

• Latch & Spinlock-Analysis

• Capture the N‘th occurrence of a specified event

• Trace details of the new technologies that came into SQL Server since 2008 (!)

• Correlate OS and SQL Server Activity End-to-end (E2E) tracing using Client API

Correlation ID between client and server from SNAC11 / TDS 7.4 onwards

SQL Trace vs. XEvents

SQL Server Extended Events are not a replacement for Profiler/Server Side Trace. Extended Events are a lot more than that. Extended Events provide a set of methods for collecting different events from SQL Server and correlating those different events within a single tool. It’s possible to grab many sets

Deadlocks + waits

Waits + lock graph

TempDB spill + query plan

The number of events in Profiler has remained the same since SQL Server 2008. The number of events in Extended Events (XE) has more than tripled.

XE gives you multiple options to save and view the data.

XE sessions are easily scriptable & portable compared to SQL Trace

SCENARIOS

Database Access without Auditing

• A very common question in forums is how one can track database- or even object-access in Standard Edition

• Locking is the key

• Every database access requires an S-Lock

• Tables will be locked with SCH-S or Schema Modification-Locks SCH-M – even under Read Uncommitted J