You may need to count events in SQL Server. This article by Jason Brimhall helps us understand the process, with examples, in this post from October.
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!
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;