深入研究SQL Server扩展事件–事件配对目标

Extended event sessions can use multiple targets to store captured information. We should use different targets depending on the type of data we are capturing and the structure of the data outcome we desire. The available targets include the following: event counter, ring buffer, event file, histogram, event tracing for Windows (ETW), and Event Pairing.

扩展事件会话可以使用多个目标来存储捕获的信息。 我们应根据要捕获的数据类型和所需数据结果的结构使用不同的目标。 可用目标包括：事件计数器，环形缓冲区，事件文件，直方图，Windows事件跟踪（ETW）和事件配对 。

Event pairing matches two events using captured data. There are many types of events that occur in pairs, such as locks, where an example pair would be an acquired lock and released lock. We can utilize the event pairing target to find and determine when a specified paired event hasn’t occurred in the matching set. Working with our example, we can track when a lock was acquired, but not released.

事件配对使用捕获的数据匹配两个事件。 成对发生的事件有很多类型，例如锁，其中示例对将是获取的锁和释放的锁。 我们可以利用事件配对目标来查找和确定匹配集中未发生指定的配对事件。 通过我们的示例，我们可以跟踪何时获得但未释放锁。

After two events are paired, both of them are discarded. Discarding matching events frees space for still unmatched events and allows for easy overview. A great example is the Mahjong game, when two tiles of the same type are matched, they are removed.

在两个事件配对后，它们都将被丢弃。 丢弃匹配事件将为尚不匹配的事件释放空间，并允许轻松查看。 一个很好的例子是麻将游戏，当两个相同类型的图块匹配时，它们将被删除。

Event pairing is an asynchronous process. The data is first kept in the memory buffer, and then sent to the target asynchronously. Usually the events are not sent immediately and specifics of the memory mechanism result in a minimal delay. The data stored within the target will be lost when the SQL Server is restarted.

事件配对是一个异步过程。 数据首先保存在内存缓冲区中，然后异步发送到目标。 通常情况下，事件不会立即发送，并且存储机制的细节会导致延迟最小。 重新启动SQL Server时，目标中存储的数据将丢失。

Within the article, we will go true the creation of an Extended Events session; the configuration parameters of the Event Pairing target, and obtain meaningful data from it.

在本文中，我们将实现扩展事件会话的创建； 事件配对目标的配置参数，并从中获取有意义的数据。

We will start with creating a common extended events session. Its aim is to capture information for SQL Server locks. These events occur when a lock is acquired on an object or when a lock is released.

我们将从创建一个公共扩展事件会话开始。 其目的是捕获SQL Server锁的信息。 当在对象上获取锁或释放锁时，会发生这些事件。

 
CREATE EVENT SESSION [XE_collect_locks] ON SERVER 
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released
GO
 

Without creating a target for the extended event session, the data is only stored in memory and we can use the SQL Server Management Studio to access it. Shown is the captured data consisting of lock_acquired and lock_released events, each one of them representing a single lock action.

无需为扩展事件会话创建目标，数据仅存储在内存中，我们可以使用SQL Server Management Studio进行访问。 显示的是由lock_acquired和lock_released事件组成的捕获数据，其中每个事件代表一个锁定动作。

As you can see, we have information about an exclusive lock being placed on a certain object within the tempdb database. Unfortunately finding, by hand, the corresponding events for the lock being released is quite challenging. During the creation of a sample table empty table nearly 17,000 lock events were captured by extended events.

如您所见，我们掌握了有关在tempdb数据库中的某个对象上放置排他锁的信息。 不幸的是，手工找到要释放的锁的相应事件非常具有挑战性。 在创建样本表空白表的过程中，扩展事件捕获了将近17,000个锁定事件。

Next, we will create an event pairing target that will match the collected data by the database_id, resource_1, resource, 2 and the transaction_id. If the target manages to match two lock events in pair, it will discard them.

接下来，我们将创建一个事件配对目标，该事件配对目标将通过database_id，resource_1，resource，2和transaction_id匹配收集的数据。 如果目标设法成对地匹配两个锁定事件，它将丢弃它们。

The configuration from the SQL Server Management Studio is quite understandable when working with event pairing targets:

使用事件配对目标时，SQL Server Management Studio的配置非常容易理解：

However, we will use the TSQL syntax and review the possible configuration parameters:

但是，我们将使用TSQL语法并查看可能的配置参数：

 
ALTER EVENT SESSION [XE_collect_locks] ON SERVER 
ADD TARGET package0.pair_matching
(SET 
begin_event=N'sqlserver.lock_acquired'
	,begin_matching_columns=N'database_id,resource_1,resource_2,transaction_id'
,end_event=N'sqlserver.lock_released'
	,end_matching_columns=N'database_id,resource_1,resource_2,transaction_id'
,max_orphans=(15000)
,respond_to_memory_pressure = 1)
GO
 

The Event pairing target has eight configuration parameters as follows:

事件配对目标具有八个配置参数，如下所示：

begin_event – an event name specifying the beginning event in a paired matched sequence, must be an event name present within the current extended event session;

begin_event –在成对的匹配序列中指定开始事件的事件名称，必须是当前扩展事件会话中存在的事件名称；

end_event – an event name specifying the ending event in a paired matched sequence, must be an event name present within the current extended event session;

end_event –在成对的匹配序列中指定结束事件的事件名称，必须是当前扩展事件会话中存在的事件名称；

begin_matching_columns – the columns to perform the event pair matching on, a comma-delimited ordered list; (begin_matching_actions – additional columns to perform the event pair matching on, a comma-delimited ordered list when using additional actions within your extended event session)

begin_matching_columns –执行事件对匹配的列，以逗号分隔的有序列表； （begin_matching_actions –用于执行事件对匹配的其他列，在扩展事件会话中使用其他操作时，以逗号分隔的有序列表）

end_matching_columns – the columns to perform the event pair matching on, a comma-delimited ordered list; (end_matching_actions – additional columns to perform the event pair matching on, a comma-delimited ordered list when using additional actions within your extended event session)

end_matching_columns –执行事件对匹配的列，以逗号分隔的有序列表； （end_matching_actions –用于执行事件对匹配的其他列，在扩展事件会话中使用其他操作时，以逗号分隔的有序列表）

max_orphans – used to specify a limit for the total number of unpaired events that can be collected in the target. Once the limit is reached any unpaired events are removed starting from the oldest. The default value is 10,000.

max_orphans –用于指定可以在目标中收集的未配对事件总数的限制。 达到限制后，将从最旧的事件开始删除所有未配对的事件。 默认值为10,000。

respond_to_memory_pressure – is used to control how the target response to memory pressure events. When set to 0 it doesn’t respond; when set to 1 it stops adding new orphans to the list when there is memory pressure on the SQL Server instance.

respond_to_memory_pressure -用于控制如何目标响应内存压力事件。 设置为0时不响应； 设置为1时，如果SQL Server实例存在内存压力，它将停止向列表中添加新的孤立对象。

In order to visualize better the relation between the parameters and the events let us look the full TSQL code creating the SQL Server extended event session and the event pairing target:

为了更好地可视化参数和事件之间的关系，让我们看一下创建SQL Server扩展事件会话和事件配对目标的完整TSQL代码：

For each of the events we have within our session we will need to specify the columns to match them in the target.

对于会话中存在的每个事件，我们将需要指定列以使其与目标中的事件相匹配。

We will always capture the lock_acquired event – thus it will be our starting event, to mark it we will use the begin_event=N’sqlserver.lock_acquired’; We are also listing the columns database_id, resource_1, resource, 2 and the transaction_id;

我们将始终捕获lock_acquired事件 -因此这将是我们的开始事件，为了标记该事件，我们将使用begin_event = N'sqlserver.lock_acquired'; 我们还列出了database_id，resource_1，resource，2和transaction_id列；

Our end event will be lock_released, to mark this we will use the end_event parameter, again listing the same columns as with the begin_event. Once a lock_released event is captured, the data within the specified columns is checked if it matches one of the previously captured lock_acquired events;

我们的结束事件将是lock_released ，为此标记，我们将使用end_event参数，并再次列出与begin_event相同的列。 捕获lock_released事件后，将检查指定列中的数据是否与先前捕获的lock_acquired事件之一匹配；

If a match is found, the pair is discarded automatically. Only lock_acquired events, without a fully corresponding lock_released event, will be kept. Thеsе events are considered orphans.

如果找到匹配项，则该对将自动丢弃。 仅保留lock_acquired事件，而没有完全对应的lock_released事件。 这些事件被视为孤儿。

Note that there may be a certain delay when viewing or querying the event pairing target, the data is stored within memory and, as it is asynchronous, it is not continuously updated.

请注意，在查看或查询事件配对目标时，可能会有一定的延迟，数据存储在内存中，并且由于它是异步的，因此不会连续更新。

The data that we have collected within the event pairing target when viewed from the SQL Server Management Studio looks like this (not all columns are pictured):

从SQL Server Management Studio中查看时，我们在事件配对目标中收集的数据如下所示（并非所有列均如图所示）：

The marked rows are related to an INSERT session (with id 56) that we have started as follows:

标记的行与我们已开始的INSERT会话（ID为56）相关，如下所示：

 
BEGIN TRAN
INSERT INTO [dbo].[Test_table]
     VALUES ('Test_data')
GO
 

The full information for just session 56 is as listed below, we have exclusive (X) and indent exclusive (IX) locks present:

会话56的完整信息如下所示，我们具有排他（X）和缩进排他（IX）锁：

event_name	associated_object_id	mode	owner_type	resource_0	resource_1	resource_2	resource_type	transaction_id
lock_acquired	2089670228251830000	X	Transaction	344	196609	0	RID	90025
lock_acquired	2089670228251830000	IX	Transaction	344	1	0	PAGE	90025
lock_acquired	981578535	IX	Transaction	981578535	0	0	OBJECT	90025

event_name	related_object_id	模式	owner_type	资源_0	resource_1	资源_2	resource_type	transaction_id
lock_acquired	2089670228251830000	X	交易	344	196609	0	RID	90025
lock_acquired	2089670228251830000	九	交易	344	1个	0	页	90025
lock_acquired	981578535	九	交易	981578535	0	0	目的	90025

Once we commit the transaction using ‘COMMIT TRANSACTION’ each of the lock_acquired events will have their corresponding lock_released events and the pair of the two will be discarded from our target.

一旦我们使用' COMMIT TRANSACTION ' 提交事务 ，每个lock_acquired事件将具有其对应的lock_released事件，并且这两个对将从我们的目标中丢弃。

Using the system view ‘sys.dm_xe_session_targets’ we can view the collected data for the event pairing target:

使用系统视图“ sys.dm_xe_session_targets”，我们可以查看事件配对目标的收集数据：

 
SELECT 
name, target_name, CAST(xst.target_data AS xml) as XML_data
FROM sys.dm_xe_session_targets AS xst  
JOIN sys.dm_xe_sessions AS xs
ON (xs.address = xst.event_session_address)  
WHERE xs.name = 'XE_collect_locks' –- Specifying the Extended events session name
 

The data is stored as XML

数据存储为XML

We can query the XML data and visualize it as a SQL Server table as we have previously described in the article Deep dive into the Extended Events – Histogram target.

我们可以查询XML数据并将其可视化为SQL Server表，如我们先前在“ 深入了解扩展事件-直方图目标”一文中所述 。

With good preparation in advance and careful configuration an Extended events session with event pairing target can very efficient and useful tool to match events of interests. But poor configuration can lead to situation where a session can perform poorly and lead to potential memory pressure and performance problems.

通过预先做好准备和精心配置，带有事件配对目标的扩展事件会话可以非常有效和有用地匹配感兴趣的事件。 但是配置不当会导致会话性能下降并导致潜在的内存压力和性能问题。

The previous article in this series

本系列的上一篇文章

• Deep dive into the Extended Events – Histogram target 深入了解扩展事件–直方图目标

翻译自: https://www.sqlshack.com/deep-dive-into-sql-server-extended-events-the-event-pairing-target/