Counting Events

After reading through the articles about the pair_matching target (here) and the article on the histogram target (here), this one is going to seem very basic. To be honest, I would consider this to be basic even if it was discussed prior to the two just mentioned. This article will be diving into the shallow depths of the event_counter target.

There is no real surprise to what the function of this target is – to count. The configuration will be very basic in nature. Reading the target will be very basic in nature.

The real use here would be to count event occurrences at a step below the histogram target. Unlike the histogram, this target does not group events. It just counts. You may be asking why use this particular target. That would be a valid question.

Imagine that you just want to know how many times an event has fired but don’t want to add the full overhead of event collection. If an event fires frequently, you can get an idea as to the workload. In addition, you could determine a focus area for troubleshooting at some future point. That could come in handy!

Counting Events

Following the established pattern, before I proceed with trying to configure a session to use this target, I really need to know more about the target. I need to know what I can configure for this target. So, like in previous articles, I just need to query the metadata to find any configurable settings for the event_counter target. The following script will help me do that:

DECLARE @includeprivate TINYINT = 1 --1 public only and 2 for all
,@targetname VARCHAR(128) = 'event_counter' --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;

From this query, I now know that I have absolutely nothing to configure that is specific to the target. This reinforces the basic nature of the target.

Now comes the hard part. To effectively use this target, I really should know just what I want to count. Once I get a list of events I want to count, then I need to throw those together into a session. With that in mind, I have the following session with a handful of events that I want to start counting:

USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'CountingSheep' )
DROP EVENT SESSION CountingSheep 
    ON SERVER;
GO
CREATE EVENT SESSION [CountingSheep] ON SERVER 
ADD EVENT sqlserver.lock_deadlock(
    ACTION(sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_starting(),
ADD EVENT sqlserver.Login (),
ADD EVENT sqlserver.Logout ()
ADD TARGET package0.event_counter
WITH (STARTUP_STATE=OFF);
GO
ALTER EVENT SESSION [CountingSheep]
ON SERVER
STATE = START;

After creating that session and letting it run for a little bit in my sandbox, then I am ready to take a peek at the session data. The query to peek into the session data, at a basic level, is just like the query that has been used over the past few articles.

SELECT CAST ([target_data] AS XML) AS target_data
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs
ON xs.address = xt.event_session_address
WHERE xs.name = N'CountingSheep'
AND xt.target_name = N'event_counter'
;

And the results of that query would look something like the following:

Similar to the simplicity in configuration for the target, the data is stored in a very simple manner. In the target, I will only see a count for the event if the event has fired during the session. Otherwise, the entire event node for the event in question will be missing and just the parent package of the event will exist in the data. Notice that there is a node for every package within the Extended Events engine. Most have no events in this session. Had an event for the package been in the session and not fired, I may see something like the following (which happens to be this same session just prior to a deadlock occurrence):

This is handy information and seems to make sense. If an event never fires, why have a node for the data for that event in the session. Sadly, the logic for the packages is a bit reversed. All packages are included regardless. That said, having a placeholder already there can make it a little more efficient for the firing event to be recorded into the target.

With a solid (maybe semi-solid) understanding of what the structure of the target data is, it is time to start parsing the target data into something a tad easier to read and understand.

SELECT CAST ([target_data] AS XML) AS target_data
INTO #xmlpreprocess
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs
ON xs.address = xt.event_session_address
WHERE xs.name = N'CountingSheep'
AND xt.target_name = N'event_counter'
;
-- Query the XML variable to get the Target Data
SELECT pck.package_name, evt.event_name, evt.event_count
FROM (
SELECT event_data.value('(../@name)[1]', 'varchar(50)') AS package_name
, event_data.value('(@name)[1]', 'varchar(50)') AS event_name
, event_data.value('(@count)[1]', 'bigint') AS event_count
FROM #xmlpreprocess x
CROSS APPLY x.target_data.nodes('/CounterTarget/Packages/Package/Event') AS EvntData ( event_data )
) evt
RIGHT OUTER JOIN (
SELECT DISTINCT pack_data.value('(@name)[1]', 'varchar(50)') AS package_name
FROM #xmlpreprocess x
CROSS APPLY x.target_data.nodes('//CounterTarget/Packages/Package') AS packData ( pack_data )
) pck
ON evt.package_name = pck.package_name
ORDER BY evt.event_count DESC, evt.event_name, evt.package_name
GO

This will give me the following results in my sandbox:

As is shown in the results, I have results for each of the events specified in the Session. These are the same events and event counts seen in the XML, just in a more friendly format. Most packages have no data due to a lack of events from that package being included in the session.

For this particular server, the only thing that may be of interest is the deadlock occurrence. But since that was caused by a controlled experiment that forced the deadlock, I won’t worry about it. Had this been a production environment, the deadlock may need to be investigated.

Looking a little closer at the query to parse the session data, one could see that it seems rather kludgy. There is probably a better way of parsing the XML, but this was the method I could get to work with my lack of XML / Xquery ability. If you happen to know of a better way to get that data properly correlated from Xquery, I would welcome that in the comments.

In this article I have just shown how to use and configure the event_counter target. In addition, I explained how to get to the data trapped into this target. Lastly, I demonstrated how to find the pertinent information for the target configurations.

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.