扩展事件概述Extended events overview

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL 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.

可在此处查找扩展事件的详细信息:快速入门:SQL Server 中的扩展事件You can find more information about Extended Events at Quickstart: Extended events in SQL Server.

SQL ServerSQL Server 扩展事件的优点Benefits of SQL ServerSQL 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 ServerSQL Server 扩展事件是在现有概念(例如事件或事件使用者)的基础上建立的,它使用了 Windows 事件跟踪的概念并引入了新概念。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.

主题Topic 描述Description
SQL Server 扩展事件包SQL Server Extended Events Packages 描述包含对象的扩展事件包。Describes the Extended Events packages that contain objects. 这些对象用于在扩展事件会话运行时获取和处理数据。These objects are used to obtain and process data when an Extended Events session is running.
SQL Server 扩展事件目标SQL Server Extended Events Targets 介绍了在事件会话期间可接收数据的事件使用者。Describes the event consumers that can receive data during an event session.
SQL Server 扩展事件引擎SQL Server Extended Events Engine 介绍了可实现和管理扩展事件会话的引擎。Describes the engine that implements and manages an Extended Events session.
SQL Server 扩展事件会话SQL Server Extended Events Sessions 介绍了扩展事件会话。Describes the Extended Events session.
   

扩展事件体系结构Extended Events Architecture

扩展事件是我们所谓的用于服务器系统的常规事件处理系统。Extended Events is our name for a general event-handling system for server systems. 扩展事件基础结构支持 SQL ServerSQL Server中数据的关联,并且在某些情况下,还支持操作系统和数据库应用程序中数据的关联。The Extended Events infrastructure supports the correlation of data from SQL ServerSQL Server, and under certain conditions, the correlation of data from the operating system and database applications. 在操作系统的情况下,必须将扩展事件输出定向到 Windows 事件跟踪 (ETW)。In the operating system case, Extended Events output must be directed to Event Tracing for Windows (ETW). 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:

  • 跟踪工具,如 SQL 跟踪和系统监视器。Tracing tools, such as SQL Trace and System Monitor.

  • 日志记录工具,如 Windows 事件日志或 SQL ServerSQL Server 错误日志。Logging tools, such as the Windows event log or the SQL ServerSQL 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. 有关扩展事件引擎的详细信息,请参阅 SQL Server Extended Events EngineFor 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. 有关详细信息,请参阅 SQL Server Extended Events TargetsFor 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. 有关详细信息,请参阅 SQL Server Extended Events PackagesFor 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.

  • 与现有的 ETW 工具集成并支持现有的 ETW 工具。Integration with, and support for existing ETW tools.

  • 基于 Transact-SQLTransact-SQL的完全可配置的事件处理机制。A fully configurable event handling mechanism that is based on Transact-SQLTransact-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. 有关详细信息,请参阅 使用 system_health 会话For more information, see Use the system_health Session.

扩展事件任务Extended Events Tasks

使用 Management StudioManagement StudioTransact-SQLTransact-SQL 执行 Transact-SQLTransact-SQL 数据定义语言 (DDL) 语句、动态管理视图和功能或目录视图,可以针对 SQL ServerSQL Server 环境创建简单或复杂的 SQL ServerSQL Server 扩展事件排除故障解决方案。Using Management StudioManagement Studio or Transact-SQLTransact-SQL to execute Transact-SQLTransact-SQL Data Definition Language (DDL) statements, dynamic management views and functions, or catalog views, you can create simple or complex SQL ServerSQL Server Extended Events troubleshooting solutions for your SQL ServerSQL Server environment.

任务说明Task Description 主题Topic
使用 “对象资源管理器” 管理事件会话。Use the Object Explorer to manage event sessions. 在对象资源管理器中管理事件会话Manage Event Sessions in the Object Explorer
介绍如何创建扩展事件会话。Describes how to create an Extended Events session. 创建扩展事件会话Create an Extended Events Session
介绍如何查看和刷新目标数据。Describes how to view and refresh target data. SQL Server 中扩展事件的目标数据的高级查看功能Advanced Viewing of Target Data from Extended Events in SQL Server
介绍如何使用扩展事件工具创建和管理 SQL ServerSQL Server 扩展事件会话。Describes how to use Extended Events tools to create and manage your SQL ServerSQL Server Extended Events sessions. 扩展事件工具Extended Events Tools
介绍如何更改扩展事件会话。Describes how to alter an Extended Events session. 更改扩展事件会话Alter an Extended Events Session
介绍如何获取与事件关联的字段的信息。Describes how to get information about the fields associated with the events. 获取所有事件的字段Get the Fields for All Events
介绍如何找到在已注册的包中有哪些事件可用。Describes how to find out what events are available in the registered packages. 查看已注册包的事件View the Events for Registered Packages
介绍如何确定在已注册的包中有哪些扩展事件目标可用。Describes how to determine what Extended Events targets are available in the registered packages. 查看已注册包的扩展事件目标View the Extended Events Targets for Registered Packages
介绍如何查看与各 SQL 跟踪事件及其关联列等效的扩展事件和操作。Describes how to view the Extended Events events and actions that are equivalent to each SQL Trace event and its associated columns. 查看与 SQL 跟踪事件类等效的扩展事件View the Extended Events Equivalents to SQL Trace Event Classes
介绍如何找到在 CREATE EVENT SESSION 或 ALTER EVENT SESSION 中使用 ADD TARGET 参数时可设置的参数。Describes how to find the parameters you can set when you use the ADD TARGET argument in CREATE EVENT SESSION or ALTER EVENT SESSION. 获取 ADD TARGET 实参的可配置形参Get the Configurable Parameters for the ADD TARGET Argument
介绍如何将现有 SQL 跟踪脚本转换为扩展事件会话。Describes how to convert an existing SQL Trace script to an Extended Events session. 将现有 SQL 跟踪脚本转换为扩展事件会话Convert an Existing SQL Trace Script to an Extended Events Session
介绍如何确定持有锁的查询、查询的计划以及取锁时的 Transact-SQLTransact-SQL 堆栈。Describes how to determine which queries are holding the lock, the plan of the query, and the Transact-SQLTransact-SQL stack at the time the lock was taken. 确定持有锁的查询Determine Which Queries Are Holding Locks
介绍如何识别影响数据库性能的锁来源。Describes how to identify the source of locks that are hindering database performance. 查找具有最多锁定的对象Find the Objects That Have the Most Locks Taken on Them
介绍如何将扩展事件和 Windows 事件跟踪配合使用来监视系统活动。Describes how to use Extended Events with Event Tracing for Windows to monitor system activity. 使用扩展事件监视系统活动Monitor System Activity Using Extended Events
为扩展事件使用目录视图和动态管理视图 (DMV)Using the Catalog views and the Dynamic management views (DMVs) for extended events SQL Server 中扩展事件系统视图中的 SELECT 和 JOINSELECTs and JOINs From System Views for Extended Events in SQL Server
   

使用以下 Transact-SQL (T-SQL) 查询列出所有可能的扩展事件及其说明: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

Azure SQL 数据库的代码示例可能有所不同Code examples can differ for Azure SQL Database

为本地 SQL Server 编写的一些 Transact-SQL 代码示例需要少量更改才能在云中的 Azure SQL 数据库服务上运行。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_   -   前缀 server_   -   prefix for on-premises
  • 用于云中的 Azure SQL DB 服务的 database_   -   前缀 database_   -   prefix for Azure SQL DB service in the cloud

为便于说明,下表列出并比较了系统视图的两个子集。For illustration, the following table lists and compares two subsets of the system views. 为简洁起见,将子集限制为也包含字符串 _event 的视图名称。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.

本地 2017 中的名称Name from on-premises 2017 云服务中的名称Name from cloud service
server_event_notificationsserver_event_notifications
server_event_session_actionsserver_event_session_actions
server_event_session_eventsserver_event_session_events
server_event_session_fieldsserver_event_session_fields
server_event_session_targetsserver_event_session_targets
server_event_sessionsserver_event_sessions
server_eventsserver_events
server_trigger_eventsserver_trigger_events
database_event_session_actionsdatabase_event_session_actions
database_event_session_eventsdatabase_event_session_events
database_event_session_fieldsdatabase_event_session_fields
database_event_session_targetsdatabase_event_session_targets
database_event_sessionsdatabase_event_sessions
   

上表中的两个列表精确到 2019 年 6 月。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. 有关准确的列表,请运行以下 T-SQL SELECT 语句。For accurate lists, run the following T-SQL SELECT statement. 对每个数据库系统各运行一次 SELECT。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
对 SQL Server 对象和版本的 DAC 支持DAC Support For SQL Server Objects and Versions
部署数据层应用程序Deploy a Data-tier Application
监视数据层应用程序Monitor Data-tier Applications
 
扩展事件动态管理视图Extended Events Dynamic Management Views
扩展事件目录视图 (Transact-SQL)Extended Events Catalog Views (Transact-SQL)
 
XELite:跨平台库,用于从 XEL 文件或实时 SQL 流中读取 XEvent,发布于 2019 年 5 月。XELite: Cross-platform library to read XEvents from XEL files or live SQL streams, released May 2019.
Read-SQLXEvent PowerShell cmdlet,发布于 2019 年 6 月。Read-SQLXEvent PowerShell cmdlet, released June 2019.
SQL 意外:因果关系跟踪与 XEvent 会话的事件顺序(博客发布于 2019 年 4 月 1 日)SQL Mysteries: Causality tracking vs Event Sequence for XEvent Sessions (blog published April 1, 2019)