An XEvent a Day (8 of 31) – Targets Week – synchronous_event_counter

Yesterday’s post, Targets Week – Bucketizers, looked at the bucketizer Targets in Extended Events and how they can be used to simplify analysis and perform more targeted analysis based on their output.  Today’s post will be fairly short, by comparison to the previous posts, while we look at the synchronous_event_counter target, which can be used to test the impact of an Event Session without actually incurring the cost of Event collection.

What is the synchronous_event_counter?

The synchronous_event_count simply put, is a Target that counts the number of Events that fire for a given Event Session.  It can be used to test whether or not the defined Predicates on Events in an Event Session perform the level of filtering expected, without having to actually perform full Event collection using one of the raw Event data targets like the ring_buffer or asynchronous_file_target.  The Target is synchronous however, due to the fact that it only counts the number of times each Event fires, its impact is minimized in comparison to the other synchronous targets available.  Like the ring_buffer and bucketizer, the synchronous_event_counter Target is a memory resident Target that holds event data in memory while the Event Session is active on the SQL Server.  When the Event Session is stopped, the memory buffers allocated to the synchronous_event_counter target are freed and any information contained in the target is lost.

Configuration Options

There are no configuration options for the synchronous_event_counter Target. (see I said this was going to be a short post comparatively).

 -- Target Configurable Fields
SELECT 
   oc.name AS column_name,
   oc.column_id,
   oc.type_name,
   oc.capabilities_desc,
   oc.description
FROM sys.dm_xe_packages AS p
JOIN sys.dm_xe_objects AS o 
    ON p.guid = o.package_guid
JOIN sys.dm_xe_object_columns AS oc 
    ON o.name = oc.OBJECT_NAME 
   AND o.package_guid = oc.object_package_guid
WHERE(p.capabilities IS NULL OR p.capabilities & 1 = 0)
  AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
  AND o.object_type = 'target'
  AND o.name = 'synchronous_event_counter'

Understanding the Target Data Format

Like the ring_buffer and bucketizer Targets, the synchronous_event_counter Target returns its information by querying the sys.dm_xe_session_targets DMV, and it returns the Target data in an XML format that is not schema bound, but that has a standardized format.  The synchronous_event_counter Target has a very simple XML document, much like the bucketizer Targets.  The root node of the XML is the <CounterTarget> node which has a child node <Packages> which has a child <Package> node for each package that identifies the package using an @name attribute.  Each <Package> node will have one or more <Event> nodes based on the number of Events defined in the Event Session for that particular package.  The <Event> nodes each will contain two attributes, the name of the event and the count for its occurrence since the Event Session started.  A simplified representation of the XML output by the synchronous_event_counter Target is below:

<CounterTarget truncated="">
  <Packages>
    <Package name="">
      <Event name="" count="" />
    </Package>
  </Packages>
</CounterTarget>

Querying/Parsing the Target Data

Like the other memory resident Targets in Extended Events, the synchronous_event_counter Target data is only exposed by querying the sys.dm_xe_session_targets DMV.  The following example will demonstrate how the synchronous_event_counter can be used to test the number of Events that an Event Session will generate:

-- Create an Event Session to Track Recompiles
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='CounterTargetDemo')
    DROP EVENT SESSION [CounterTargetDemo] ON SERVER;
CREATE EVENT SESSION [CounterTargetDemo]
ON SERVER
ADD EVENT sqlserver.sql_statement_starting,
ADD EVENT sqlos.wait_info
(    WHERE (duration > 0))
ADD TARGET package0.synchronous_event_counter
GO

-- Start the Event Session
ALTER EVENT SESSION [CounterTargetDemo]
ON SERVER
STATE=STOP
GO

-- Wait for Events to generate and then Query Target

-- Query the Target
SELECT 
    n.value('../@name[1]', 'varchar(50)') as PackageName,
    n.value('@name[1]', 'varchar(50)') as EventName,
    n.value('@count[1]', 'int') as Occurence
FROM
(
SELECT CAST(target_data AS XML) as target_data
FROM sys.dm_xe_sessions AS s 
JOIN sys.dm_xe_session_targets AS t 
    ON t.event_session_address = s.address
WHERE s.name = 'CounterTargetDemo'
  AND t.target_name = 'synchronous_event_counter'
) as tab
CROSS APPLY target_data.nodes('CounterTarget/Packages/Package/Event') as q(n)

-- Drop the Event Session
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='CounterTargetDemo')
    DROP EVENT SESSION [CounterTargetDemo] ON SERVER;

This session on one of my test servers generated the following output while running for less than five seconds:

Based on this number of Events being fired, it may be determined that the predicates for the session need to provide further filtering of the Events, or if it is determined that the Predicates are filtering the Events as intended, this at least lets us know that the number of Events firing will require the use of the asynchronous_file_target, if the plan is to look at the Events raw data.

Considerations for Usage

The only real consideration associated with the synchronous_event_counter Target is that it is an synchronous Target.  However, since it is only counting the occurrences of the Events defined in the Event Session and is not actually buffering the data for dispatch its impact is not generally a concern.

What’s next?

Now that we have looked at the ring_buffer, asynchronous_file_target, bucketizers, and synchronous_file_target Targets, in the next post we’ll look at the pair_matching Target, which can be used to match up related Events based on specific criteria to discard the matched pairs, leaving unmatched and potentially problematic Events for analysis.