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.
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.
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.
|
1 2 3 4 5 6 |
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.
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.
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.
The configuration from the SQL Server Management Studio is quite understandable when working with event pairing targets:
However, we will use the TSQL syntax and review the possible configuration parameters:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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;
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;
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)
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)
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.
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.
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:
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;
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;
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.
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):
The marked rows are related to an INSERT session (with id 56) that we have started as follows:
|
1 2 3 4 5 6 |
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:
| 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 |
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.
Using the system view ‘sys.dm_xe_session_targets’ we can view the collected data for the event pairing target:
|
1 2 3 4 5 6 7 8 |
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
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.
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
View all posts by Kaloyan Kosev
- Performance tuning for Azure SQL Databases - July 21, 2017
- Deep dive into SQL Server Extended events – The Event Pairing target - May 30, 2017
- Deep dive into the Extended Events – Histogram target - May 24, 2017