Extended events overview

• 07/23/2019
• 6 minutes to read
• • 
  • 
  • 
  • 
  • 
  • +2

APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

SQL Server The Extended Events architecture enables users to collect as much or as little data as is necessary to troubleshoot or identify a performance problem. Extended Events is configurable, and it scales very well.

You can find more information about Extended Events at Quickstart: Extended events in SQL Server.

Benefits of SQL Server Extended Events

Extended Events is a lightweight performance monitoring system that uses minimal performance resources. Extended Events provides two graphical user interfaces to create, modify, display, and analyze your session data. These interfaces are named:

• New Session Wizard
• New Session

Extended Events Concepts

SQL Server Extended Events builds on existing concepts, such as an event or an event consumer, uses concepts from Event Tracing for Windows, and introduces new concepts.

The following table describes the concepts in Extended Events.

Extended Events Architecture

Extended Events is our name for a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the operating system case, Extended Events output must be directed to Event Tracing for Windows (ETW). ETW can correlate the event data with operating system or application event data.

All applications have execution points that are useful both inside and outside an application. Inside the application, asynchronous processing may be enqueued using information that is collected during the initial execution of a task. Outside the application, execution points provide monitoring utilities with information. The information is about the behavioral and performance characteristics of the monitored application.

Extended Events supports using event data outside a process. This data is typically used by:

• Tracing tools, such as SQL Trace and System Monitor.

• Logging tools, such as the Windows event log or the SQL Server error log.

• Users administering a product or developing applications on a product.

Extended Events has the following key design aspects:

• The Extended Events engine is event agnostic. The engine can bind any event to any target, because the engine is not constrained by event content. For more information about the Extended Events engine, see SQL Server Extended Events Engine.

• Events are separated from event consumers, which are called targets in Extended Events. This means that any target can receive any event. In addition, any event that is raised can be automatically consumed by the target, which can log or provide additional event context. For more information, see SQL Server Extended Events Targets.

• Events are distinct from the action to take when an event occurs. Therefore, any action can be associated with any event.

• Predicates can dynamically filter when event data should be captured. Dynamic filtering adds to the flexibility of the Extended Events infrastructure. For more information, see SQL Server Extended Events Packages.

Extended Events can synchronously generate event data (and asynchronously process that data) which provides a flexible solution for event handling. In addition, Extended Events provides the following features:

• A unified approach to handling events across the server system, while enabling users to isolate specific events for troubleshooting purposes.

• Integration with, and support for existing ETW tools.

• A fully configurable event handling mechanism that is based on Transact-SQL.

• The ability to dynamically monitor active processes, while having minimal effect on those processes.

• A default system health session that runs without any noticeable performance effects. The session collects system data that you can use to help troubleshoot performance issues. For more information, see Use the system_health Session.

Extended Events Tasks

Using Management Studio or Transact-SQL to execute Transact-SQL Data Definition Language (DDL) statements, dynamic management views and functions, or catalog views, you can create simple or complex SQL Server Extended Events troubleshooting solutions for your SQL Server environment.

Use the following Transact-SQL (T-SQL) query to list out all possible extended events and their descriptions:

SELECT
     obj1.name as [XEvent-name],
     col2.name as [XEvent-column],
     obj1.description as [Descr-name],
     col2.description as [Descr-column]
  FROM
               sys.dm_xe_objects        as obj1
      JOIN sys.dm_xe_object_columns as col2 on col2.object_name = obj1.name
  ORDER BY
    obj1.name,
    col2.name

Code examples can differ for Azure SQL Database

Some Transact-SQL code examples written for SQL Server on-premises need small changes to run on Azure SQL Database service in the cloud. One catagory of such code examples involves system views whose name prefixess differ slightly between the two database systems:

• server_   -   prefix for on-premises
• database_   -   prefix for Azure SQL DB service in the cloud

For illustration, the following table lists and compares two subsets of the system views. For brevity, the subsets are restricted to view names that also contains the string _event. The subsets have differing name prefixes because they come the two different database systems.

The two lists in the preceding table are accurate as of June 2019. But the table contents here may become outdated, because its content will not be maintained here. For accurate lists, run the following T-SQL SELECT statement. Run the SELECT twice, once on each database system.

SELECT name
    FROM sys.all_objects
    WHERE
        (name LIKE 'database\_%' { ESCAPE '\' } OR
         name LIKE 'server\_%' { ESCAPE '\' })
        AND name LIKE '%\_event%' { ESCAPE '\' }
	    AND type = 'V'
    ORDER BY name;

See Also

Data-tier Applications
DAC Support For SQL Server Objects and Versions
Deploy a Data-tier Application
Monitor Data-tier Applications
 
Extended Events Dynamic Management Views
Extended Events Catalog Views (Transact-SQL)
 
XELite: Cross-platform library to read XEvents from XEL files or live SQL streams, released May 2019.
Read-SQLXEvent PowerShell cmdlet, released June 2019.
SQL Mysteries: Causality tracking vs Event Sequence for XEvent Sessions (blog published April 1, 2019)