扩展事件概述Extended events overview

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

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

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

SQL ServerSQL Server 扩展事件的优点Benefits of SQL ServerSQL Server Extended Events

扩展事件是使用非常少的性能资源的轻型性能监视系统。Extended Events is a light weight performance monitoring system that uses very few performance resources. 扩展事件提供两个图形用户界面(“新建会话向导” 和“新建会话” ),以便创建、修改、显示和分析你的会话数据。Extended Events provides two graphical user interfaces (New Session Wizard and New Session) to create, modify, display, and analyze your session data.

扩展事件概念Extended Events Concepts

SQL ServerSQL Server 扩展事件 (Extended Events) 是在现有概念(例如事件或事件使用者)的基础上建立的,它使用了 Windows 事件跟踪的概念并引入了新概念。Extended Events (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 that are used for obtaining and processing 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) 是用于服务器系统的常规事件处理系统。Extended Events (Extended Events) is 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 latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) to 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 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. 因此,该引擎可以将任何事件绑定到任何目标,因为该引擎不受事件内容约束。This enables the engine to 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. 这增加了扩展事件基础结构的灵活性。This 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

Azure SQL 数据库的代码示例可能有所不同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.

Name from on-premises 2017 Name from cloud service
server_event_notifications
server_event_session_actions
server_event_session_events
server_event_session_fields
server_event_session_targets
server_event_sessions
server_events
server_trigger_events
database_event_session_actions
database_event_session_events
database_event_session_fields
database_event_session_targets
database_event_sessions
   

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 对 SQL Server 对象和版本的支持 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.