Azure SQL 数据库中的扩展事件Extended events in Azure SQL Database

适用于: Azure SQL 数据库

Azure SQL 数据库中扩展事件的功能集是 SQL Server 和 Azure SQL 托管实例上强大功能的子集。The feature set of extended events in Azure SQL Database is a robust subset of the features on SQL Server and Azure SQL Managed Instance.

XEvents 不是正式名称,有时在博客或其他非正式场合表示“扩展的事件”。XEvents is an informal nickname that is sometimes used for 'extended events' in blogs and other informal locations.

有关扩展事件的其他信息,请访问:Additional information about extended events is available at:

先决条件Prerequisites

本主题假设读者了解以下内容:This topic assumes you already have some knowledge of:

选择事件文件作为 目标时,事先了解以下项目会很有帮助:Prior exposure to the following items is helpful when choosing the Event File as the target:

代码示例Code samples

相关主题提供了两个代码示例:Related topics provide two code samples:

Transact-SQL 的差异Transact-SQL differences

  • 在 SQL Server 上执行 CREATE EVENT SESSION 命令时,请使用 ON SERVER 子句。When you execute the CREATE EVENT SESSION command on SQL Server, you use the ON SERVER clause. 但在 Azure SQL 数据库上,应改用 ON DATABASE 子句。But on Azure SQL Database you use the ON DATABASE clause instead.

  • ON DATABASE 子句也适用于 ALTER EVENT SESSIONDROP EVENT SESSION Transact-SQL 命令。The ON DATABASE clause also applies to the ALTER EVENT SESSION and DROP EVENT SESSION Transact-SQL commands.

  • 最佳做法是在 CREATE EVENT SESSIONALTER EVENT SESSION 语句中包含 STARTUP_STATE = ON 的事件会话选项。A best practice is to include the event session option of STARTUP_STATE = ON in your CREATE EVENT SESSION or ALTER EVENT SESSION statements.

    • = ON 值支持在由于故障转移而重新配置逻辑数据库之后自动重新启动。The = ON value supports an automatic restart after a reconfiguration of the logical database due to a failover.

新的目录视图New catalog views

扩展事件功能受多个目录视图的支持。The extended events feature is supported by several catalog views. 目录视图显示有关当前数据库中用户创建的事件会话的 元数据或定义 的信息。Catalog views tell you about metadata or definitions of user-created event sessions in the current database. 视图不会返回有关活动事件会话的实例的信息。The views do not return information about instances of active event sessions.

目录Name of
视图的名称catalog view
说明Description
sys.database_event_session_actionssys.database_event_session_actions 返回针对事件会话的每个事件执行的每个操作所对应的行。Returns a row for each action on each event of an event session.
sys.database_event_session_eventssys.database_event_session_events 返回事件会话中每个事件所对应的行。Returns a row for each event in an event session.
sys.database_event_session_fieldssys.database_event_session_fields 返回针对事件和目标上显式设置的每个可自定义列所对应的行。Returns a row for each customize-able column that was explicitly set on events and targets.
sys.database_event_session_targetssys.database_event_session_targets 返回事件会话的每个事件目标所对应的行。Returns a row for each event target for an event session.
sys.database_event_sessionssys.database_event_sessions 返回数据库中每个事件会话所对应的行。Returns a row for each event session in the database.

在 Microsoft SQL Server 中,类似目录视图的名称包含 .server_ 而不是 .database_In Microsoft SQL Server, similar catalog views have names that include .server_ instead of .database_ . 名称模式类似于 sys.server_event_%The name pattern is like sys.server_event_% .

新的动态管理视图 (DMV)New dynamic management views (DMVs)

Azure SQL 数据库具有支持扩展事件的动态管理视图 (DMV)Azure SQL Database has dynamic management views (DMVs) that support extended events. DMV 显示有关 活动 事件会话的信息。DMVs tell you about active event sessions.

DMV 的名称Name of DMV 说明Description
sys.dm_xe_database_session_event_actionssys.dm_xe_database_session_event_actions 返回有关事件会话操作的信息。Returns information about event session actions.
sys.dm_xe_database_session_eventssys.dm_xe_database_session_events 返回有关会话事件的信息。Returns information about session events.
sys.dm_xe_database_session_object_columnssys.dm_xe_database_session_object_columns 显示绑定到会话的对象的配置值。Shows the configuration values for objects that are bound to a session.
sys.dm_xe_database_session_targetssys.dm_xe_database_session_targets 返回有关会话目标的信息。Returns information about session targets.
sys.dm_xe_database_sessionssys.dm_xe_database_sessions 返回划归到当前数据库的每个事件会话所对应的行。Returns a row for each event session that is scoped to the current database.

在 Microsoft SQL Server 中,类似目录视图的名称不包含 _database 部分,例如:In Microsoft SQL Server, similar catalog views are named without the _database portion of the name, such as:

  • sys.dm_xe_sessions 而不是名称sys.dm_xe_sessions , instead of name
    sys.dm_xe_database_sessionssys.dm_xe_database_sessions .

两者通用的 DMVDMVs common to both

对于扩展的事件,有通用于 Azure SQL 数据库、Azure SQL 托管实例和 Microsoft SQL Server 的其他 DMV:For extended events there are additional DMVs that are common to Azure SQL Database, Azure SQL Managed Instance, and Microsoft SQL Server:

  • sys.dm_xe_map_valuessys.dm_xe_map_values
  • sys.dm_xe_object_columnssys.dm_xe_object_columns
  • sys.dm_xe_objectssys.dm_xe_objects
  • sys.dm_xe_packagessys.dm_xe_packages

查找可用的扩展事件、操作和目标Find the available extended events, actions, and targets

可运行简单的 SQL SELECT 来获取可用事件、操作和目标的列表。You can run a simple SQL SELECT to obtain a list of the available events, actions, and target.

SELECT
        o.object_type,
        p.name         AS [package_name],
        o.name         AS [db_object_name],
        o.description  AS [db_obj_description]
    FROM
                   sys.dm_xe_objects  AS o
        INNER JOIN sys.dm_xe_packages AS p  ON p.guid = o.package_guid
    WHERE
        o.object_type in
            (
            'action',  'event',  'target'
            )
    ORDER BY
        o.object_type,
        p.name,
        o.name;

  

Azure SQL 数据库事件会话的目标Targets for your Azure SQL Database event sessions

可从 Azure SQL 数据库上的事件会话捕获结果的目标如下:Here are targets that can capture results from your event sessions on Azure SQL Database:

Windows 事件跟踪 (ETW) API 不适用于 Azure SQL 数据库上的扩展事件。The Event Tracing for Windows (ETW) API is not available for extended events on Azure SQL Database.

限制Restrictions

有几个安全相关的差异适用于 Azure SQL 数据库的云环境:There are a couple of security-related differences befitting the cloud environment of Azure SQL Database:

  • 扩展事件在单租户隔离模型中构建。Extended events are founded on the single-tenant isolation model. 一个数据库中的事件会话无法访问另一个数据库中的数据或事件。An event session in one database cannot access data or events from another database.
  • 无法在 master 数据库的上下文中发出 CREATE EVENT SESSION 语句。You cannot issue a CREATE EVENT SESSION statement in the context of the master database.

权限模型Permission model

必须拥有数据库的 控制 权限才能发出 CREATE EVENT SESSION 语句。You must have Control permission on the database to issue a CREATE EVENT SESSION statement. 数据库所有者 (dbo) 拥有 控制 权限。The database owner (dbo) has Control permission.

存储容器授权Storage container authorizations

针对 Azure 存储容器生成的 SAS 令牌必须为权限指定 rwlThe SAS token you generate for your Azure Storage container must specify rwl for the permissions. rwl 值提供以下权限:The rwl value provides the following permissions:

  • 读取Read
  • 写入Write
  • 列出List

性能注意事项Performance considerations

在某些情况下,大量使用扩展事件可能累积过多的活动内存,使整个系统无法正常运行。There are scenarios where intensive use of extended events can accumulate more active memory than is healthy for the overall system. 因此,Azure SQL 数据库会动态设置和调整事件会话可以累积的活动内存量限制。Therefore Azure SQL Database dynamically sets and adjusts limits on the amount of active memory that can be accumulated by an event session. 动态计算会考虑许多因素。Many factors go into the dynamic calculation.

如果收到错误消息,指出已强制实施内存最大值,可采取以下纠正措施:If you receive an error message that says a memory maximum was enforced, some corrective actions you can take are:

  • 减少运行的并发事件会话。Run fewer concurrent event sessions.
  • 通过对事件会话执行 CREATEALTER 语句,减少在 MAX_MEMORY 子句中指定的内存量。Through your CREATE and ALTER statements for event sessions, reduce the amount of memory you specify on the MAX_MEMORY clause.

网络延迟Network latency

事件文件 目标在将数据保存到 Azure 存储 Blob 时可能会遇到网络延迟或故障。The Event File target might experience network latency or failures while persisting data to Azure Storage blobs. Azure SQL 数据库中的其他事件可能会延迟,因为它们要等待网络通信完成。Other events in Azure SQL Database might be delayed while they wait for the network communication to complete. 这种延迟可能会导致工作负荷变慢。This delay can slow your workload.

  • 若要缓解这种性能风险,请避免在事件会话定义中将 EVENT_RETENTION_MODE 选项设为 NO_EVENT_LOSSTo mitigate this performance risk, avoid setting the EVENT_RETENTION_MODE option to NO_EVENT_LOSS in your event session definitions.