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.
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', 'varchar(50)') as PackageName, n.value('@name', 'varchar(50)') as EventName, n.value('@count', '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.
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.