By: Ben Snaidero
Overview
When the Extended Events feature was first introduced in SQL Server 2008 many people simply looked at it as a replacement for SQL Server Profiler and Tracing. It is however much more than that as it was designed from scratch to both include the functionality available in these older tools as well as provide a framework so that more events could be easily added as required.
Extended Events gives you the ability to monitor and collect different events and system information from SQL Server and correlate these events for later analysis. It is much easier to perform analysis using Extended Events as all the data is collected in a single source. Also, as newer features have been added to SQL Server over the years, such as AlwaysON and ColumnStore indexes as examples, events to monitor these features are only available in extended events and are not available in SQL Profiler or SQL Trace. SQL Server 2016 has over 1320 Extended Events whereas every version since SQL Server 2008 has had only 180 SQL Trace events. Also, since it was designed from scratch, monitoring using Extended Events has been shown to have less resource overhead than its older counterparts.
With its initial release in 2008 there was no GUI available to manage Extended Events so database administrators had to configure it using TSQL which was not the most straightforward task. This led to many people (myself included) to still use the original trace tools for the most part. Starting with SQL Server 2012 a GUI tool was introduced which allowed you to configure events and even perform some analysis from within the interface. SSMS v17 even added XEvent Profiler which allows you to configure a real-time monitor similar to how SQL Profiler was used. This along with all the other features mentioned above has more and more database professionals using Extended Events to monitor their SQL Server environments.
In the rest of this tutorial we will walk through everything you need to know in order to start using Extended Events to monitor you own SQL Server environments. Topics will include the following:
- Architecture
- Creating Extended Event Sessions
- Managing Extended Event Sessions
- Viewing Extended Events Target Data
- Extended Events vs. SQL Profiler/Trace
- Built-in Extended Event Sessions
- Sample Custom Extended Event Sessions
Additional Information
- Tips on Extended Events
- An Overview of Extended Events in SQL Server
- Getting Started with Extended Events in SQL Server
