How to Count Events in SQL Server

SQL Server

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;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s