In the last article I showed how to discover all of the settings that are available to a particular Target. I also mentioned the reason for that being that I would be discussing some of the other Target types since I had already shown various concepts in regards to the ring_buffer and event_file targets.

In this article I will be taking you into the world of the pair_matching target. The pair_matching target should connote that this target works by matching like events into a single pair. Very much like a pair of socks would contain two parts, one for each foot or side of the body, the pair_matching target creates pairs of events that go together. These paired events would typically correlate to one half of the equation – or in more simple terms, they are pairs because they are opposites just like the left shoe is a match to the right shoe.

Matching Events into Pairs

What if I have more than one pair of shoes though? Well, that is part of the equation with this target. Just like I need some sort of indicator that helps me match my shoes properly into pairs, I need to have some sort of indicator with the events in SQL Server, that are firing, that can help me figure out how they are related and should be in the same pair.

The equation to figure out which events are pairs is made available through the implementation of settings. To properly configure the pair_matching target requires: first, an understanding of what the possible configuration settings are, and second, an understanding of what it is that is to be paired.

To figure out what the possible configuration settings are, I will adapt a query from the previous article and use it here:

DECLARE @includeprivate TINYINT = 1 --1 public only and 2 for all
,@targetname VARCHAR(128) = 'pair_matching' --null for all
SELECT xoc.object_name AS TargetName
,xoc.name AS FieldName
, xoc.capabilities_desc
,xoc.description
, xoc.column_value AS DefaultSetting
, xoc.type_name AS DataType
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_object_columns xoc
ON xo.package_guid = xoc.object_package_guid
AND xo.name = xoc.object_name
WHERE xo.object_type = 'target'
AND (xo.capabilities IS NULL
OR xo.capabilities & 1 <> @includeprivate)
AND ISNULL(@targetname, xo.name) = xo.name
ORDER BY xoc.object_name DESC;

This would produce the following results:

The previous image are the results that I get for this Target in SQL Server 2014. I am going to start with the obvious question. It was so obvious that I marked the problem area with the seemingly suitable question marks. This is a pairing target, yet the begin_event is not a mandatory setting – at least not according to the metadata. At least the end_event is mandatory! That said, it should seem pretty basic that a begin_event is necessary as part of the equation. Otherwise, what would be matched to the end_event?

Of interest, to me,  in the results for the settings is the default settings values for the begin_event, end_event, respond_to_memory_pressure, and max_orphans settings. Despite the empty string for the begin_event, I believe it should also be NULL just like the end_event. It makes sense to me that the default value is NULL in this case because these should be defined in the session and should not have a default value.

The next setting of interest is the respond_to_memory_pressure setting. The default for this is false. This means that if there are sufficient unmatched events, it could cause memory pressure and potentially have an adverse effect. By enabling this setting, unpaired events could be removed from the buffer which could lead to a higher level of orphaned events.

Last up is the default setting for the max_orphans. This is another optimization that could be enabled to help reduce the memory effects of the session.  If this limit is reached, then unpaired events will start to be removed from the buffer in a FIFO fashion.

The remaining columns wouldn’t make sense to have default values. Nor does it make sense to be set to NULL since they are more or less optional. That said, the remaining configuration options are crucial to designing the formula to efficiently match the events. If a begin_matching_actions setting is added, then the end_matching_actions should also be set. I do, however, want to note right now that the names of these settings are plural. The reason for the setting name to be in plural form is that the configuration accepts a comma delimited list of columns or actions. Just remember to keep the columns and actions in the same order for both the begin and end configurations.

In Action

That was quite a mouthful there. Sometimes it is easier to show how this works with an example. To demonstrate, I want to work with what seems like the easiest example to fit the pair_matching model – query timeouts.

USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'Timeout2' )
DROP EVENT SESSION Timeout2 
    ON SERVER;
GO
CREATE EVENT SESSION [Timeout2] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
ACTION ( sqlserver.database_name, sqlserver.nt_username,
sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack ) ),
ADD EVENT sqlserver.sql_statement_starting (
ACTION ( sqlserver.database_name, sqlserver.nt_username,
sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack ) )
ADD TARGET package0.pair_matching (  SET begin_event = N'sqlserver.sql_statement_starting'
, begin_matching_columns = 'offset,offset_end'
, begin_matching_actions = N'sqlserver.session_id, sqlserver.tsql_stack'
, end_event = N'sqlserver.sql_statement_completed'
, end_matching_columns = 'offset,offset_end'
, end_matching_actions = N'sqlserver.session_id, sqlserver.tsql_stack' )
WITH (  MAX_DISPATCH_LATENCY = 3 SECONDS
, TRACK_CAUSALITY = ON
, STARTUP_STATE = ON );
GO

Here, I have the same session with points of interest highlighted.

I have aligned the like begin and end statements together so it is easier to see that these settings accept a comma delimited list and the items are listed in the same order. And, of course, the *_matching_actions settings have actions listed that have been attached to each of the Events that are to be paired. The *_matching_columns map to payload data points within the begin and end events. If using this particular setting, the columns of interest should exist in both events being compared.

These settings, with the proper attention to detail, can lead to an efficient and useful Session to match events. When attempting to throw this kind of session together without a little thought and prep-work, one could end up with a poorly performing session and potentially memory pressure issues (remember this is a memory target type with all the bliss that comes with a memory target).

Speaking of memory, you will need to remember to tune in for the next article where I will explore how to parse the event data for this session.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.