学习SQL Server扩展事件提示

迪鲁宾
1,535 阅读7分钟

本文旨在提供一些关于SQL Server扩展事件的有益提示，使其更容易创建和使用事件会话。

简介

SQL Server 扩展事件是一个性能监控工具，它有能力收集 SQL Server 的各种事件数据。因此，它可以更容易地诊断出性能和其他问题。同时，使用SQL Server扩展事件为我们提供了以下主要优势。

  • 先进的图形化用户界面
  • 资源占用少，对数据库引擎的性能影响小
  • 有可能收集大量的事件数据

在数据资源管理器中对事件数据进行分组、排序和汇总

SQL Server扩展事件提供了一个数据资源管理器 工具，可以查看、过滤和分组捕获的数据。在分组功能的帮助下，我们可以将收集到的数据行排列成组，我们还可以对这些字段应用聚合函数（SUM, COUNT, MAX, MIN, AVG）。假设，我们从服务器上收集了已执行的查询数据，我们想检测哪些查询的物理读取量最大。作为第一步，我们点击工具栏上的分组按钮。

SQL Server extended event collected data

同时，我们可以在扩展事件菜单上找到同样的选项。

Extended Event grouping menu

点击分组按钮后，会出现一个窗口，我们可以选择我们想要分组的字段。在这个窗口中，我们只从 可用列 列表 ，选择报表字段，并将其发送到分组的列 列表中。因此，捕获的数据将根据每个查询进行分组。另外，每个组包含的查询的数量将显示在查询文本旁边的括号里。

Extended event data aggregation

我们可以看到，这种数据视图并不能帮助我们进行有意义的性能分析，只是我们可以看到一个查询被执行了多少次。我们可以在工具栏上找到聚合按钮，我们可以为不分组的字段计算出不同的聚合类型。点击该按钮后，会出现聚合窗口。在这个窗口中，我们将为physical_reads字段选择SUM聚合类型。这样，每个语句组的总物理读取值就会被显示出来。最后，我们将对每个分组的查询语句按降序排序。

Aggregation window of the extended event

点击确定按钮后，每个查询的物理读数之和将被显示出来。

Summarize the event data

system_health事件会话

system_health会话是SQL Server默认的扩展事件会话，它在数据库引擎启动时自动启动。我们可以使用这个事件来监控以下问题。

  • 死锁
  • 闩锁等待
  • 锁等待
  • 连接错误
  • 安全错误
  • 有严重性的错误

Extended event system_health

SQL Server 扩展事件 EVENT_RETENTION_MODE 选项

SQL Server为事件会话保留了缓冲内存，以暂时存储事件数据，然后将其分派到目标存储。在某些情况下，扩展事件会话产生数据的速度快得令人难以置信。然而，收集到的事件数据不能像事件数据生成速度那样被派发到事件存储中。为了克服这些类型的情况，EVENT_RETENTION_MODE设置允许在扩展事件受到压力时为数据损失设置容忍度。这样，事件会话对数据库引擎性能的影响就最小化了。

ALLOW_SINGLE_EVENT_LOSS: 这个选项是默认选项，当事件会话内存缓冲区满了，不能发送到存储目标时，允许从会话中丢弃和丢失单个事件。

ALLOW_MULTIPLE_EVENT_LOSS： 该选项对性能影响最小，当内存缓冲区满时，允许包含多个事件的整个内存缓冲区被丢弃和丢失。

NO_EVENT_LOSS: 当我们使用这个选项时，我们可以确保没有事件数据被丢失，但会导致数据库引擎性能下降。

Extended Events EVENT_RETENTION_MODE option

在动态管理视图的帮助下，我们可以获得有关活动事件会话的信息。 sys.dm_xe_sessions 动态管理视图。同时，这个视图会返回关于放弃的事件和缓冲区计数的信息。为了更清楚地理解这个概念，我们将创建两个不同的扩展事件会话。第一个是保留模式是多事件丢失，第二个是单事件丢失。这些事件捕获锁和SQL语句的执行。

CREATE EVENT SESSION [CaptureQuery_MultipleEventLoss] ON SERVER 
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released,
ADD EVENT sqlserver.lock_request_priority_state,
ADD EVENT sqlserver.query_pre_execution_showplan,
ADD EVENT sqlserver.sql_statement_completed,
ADD EVENT sqlserver.sql_statement_starting
ADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents\CaptureQuery_MultipleEventLoss.xel')
WITH (MAX_MEMORY=1024 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
ALTER EVENT SESSION CaptureQuery_MultipleEventLoss
ON SERVER  
STATE = START
GO
CREATE EVENT SESSION CaptureQuery_SingleEventLoss ON SERVER 
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released,
ADD EVENT sqlserver.lock_request_priority_state,
ADD EVENT sqlserver.query_pre_execution_showplan,
ADD EVENT sqlserver.sql_statement_completed,
ADD EVENT sqlserver.sql_statement_starting
ADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents\CaptureQuery_MultipleEventLoss.xel')
WITH (MAX_MEMORY=1024 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS ,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
ALTER EVENT SESSION CaptureQuery_SingleEventLoss
ON SERVER  
STATE = START

在创建事件会话后，我们将在Adventureworks数据库上创建一个小的工作负载。

现在我们执行下面的查询，看看有多少事件和缓冲区被这些会话丢失。

SELECT
xe.name,
    xe.total_buffer_size,
        xe.total_bytes_generated,
    xe.buffer_policy_desc,
    xe.buffer_processed_count,
xe.dropped_event_count,
xe.dropped_buffer_count ,
xe.blocked_event_fire_time,
xe.largest_event_dropped_size
FROM sys.dm_xe_sessions AS xe

dm_xe_sessions view explanation

  • name: 事件会话的名称
  • total_buffer_size: 用于在缓冲区内存储事件数据的缓冲区大小的总金额
  • total_bytes_generated: 采集数据时产生的数据大小的总量
  • buffer_policy_desc： 这一列标识了EVENT_RETENTION_MODE的设置。
  • dropped_event_count: 当事件缓冲区已满时，被丢弃的事件数量
  • dropped_buffers_count: 当事件缓冲区已满时，被丢弃的事件缓冲区的数量。

高级过滤

SQL Server扩展事件允许过滤收集的数据，但有时我们需要高级过滤选项。假设，我们有一个存储过程，我们想捕捉这个存储过程在特定数据库上由特定的会话ID执行时的情况。在这一点上，我们需要对过滤字段条款进行分组，然后用OR条件将它们分开。

extended event group filtering

首先，我们将使用sp_statement_completed事件来捕获存储过程完成的时间。作为第二步，我们需要在过滤器标签中对数据库名称和会话ID进行分组，以生成一个可以一起工作的分组条件。要做到这一点，我们一起选择这两个字段，然后右键单击这些字段，选择分组条款 选项。

Grouping the extended event filters

这一步之后，我们将用对象名称创建一个上层组，将对象名称涉及到分组过滤器中。

Using group clauses in the extended event

现在，我们将在不同的数据库中执行uspGetBillOfMaterials存储过程，查询窗口的session-id等于83。

Test the extended event

当我们检查捕获的事件数据时，我们将只看到一个在Adventureworks2019数据库中执行的事件。

Usage details of the sp_statement_completed

导出SQL Server扩展事件的捕获数据

我们可以通过SQL Server扩展事件将采集到的数据导出到不同的文件类型或SQL表中。逗号分隔值（CSV）和SQL Server扩展事件文件（XEL）是我们可以导出收集到的事件数据的两种文件类型。要将事件数据导出到一个SQL表，我们在SQL Server Management Studio（SSMS）上打开扩展事件菜单，然后选择导出到子菜单中的表选项。

Export event data

在第二步，我们需要设置目标表的设置。我们可以将事件数据导出到一个现有的表或创建一个新的表。在这个例子中，我们将给出一个新的表名并将数据导出到这个表中。

Choose destination table

设置完目标表后，事件数据将被导出到其中。

Destination table details

XEL文件类型是导出事件数据的另一个选项。我们可以将事件数据导出为XEL文件，并将其存储在本地文件系统中。只需在导出到子菜单中选择XEL文件 ...导出选项。

Export event data to XEL file

我们给导出的文件起个名字，并为其设置文件位置。

Save the XEL data

在fn_xe_file_target_read_file函数的帮助下，我们可以读取XEL文件。然而，这个函数返回的是XML格式的数据。

SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file( 'C:\CapturedEvents\NewExtendedEvent_0.xel', NULL, NULL, NULL )

How to read XEL data of the SQL Server extended event

总结

在这篇文章中，我们已经学会了如何有效地使用SQL Server扩展事件。扩展事件是SQL Server的一个诊断工具，用于检测数据库引擎的性能和其他问题。

avatar
文章
阅读
粉丝