(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Do you have ever heard about an “Event-Less” Extended Event Session? What is the idea about it? Why do you want to have an Extended Event Session without any events? Let’s talk about it…

Event Less

When you create an Extended Event Session in SQL Server, the definition of the Event Session specifies which Events, Actions, and which Targets you want to use. Imagine you want to track for example Wait Statistics for a specific SPID. In that case you could create the following simple Extended Event Session.

-- Create a new event session that collects wait statistics
CREATE EVENT SESSION CollectWaitStatistics 
ON SERVER
ADD EVENT sqlos.wait_info
(
WHERE sqlserver.session_id = 55
)
ADD TARGET package0.ring_buffer
WITH
(
MAX_DISPATCH_LATENCY = 1 SECONDS
)
GO
-- Start the Event Session
ALTER EVENT SESSION CollectWaitStatistics
ON SERVER
STATE = START
GO

As soon as you start this session, SQL Server will track all wait_info events for the given SPID and writes everything into the ring_buffer target, which is a target in memory. But besides adding events to an Extended Event Session you can also drop existing events from an Event Session. Let’s have a look at the following code.

-- Detach the registered events from the Event Session
ALTER EVENT SESSION CollectWaitStatistics
ON SERVER
DROP EVENT sqlos.wait_info
GO

As soon as you have executed this code, you have now an Event-Less Extended Event Session. You can verify that by running the following SQL statement, which returns you all active events for a given Event Session.

SELECT e.* FROM sys.dm_xe_session_events e
INNER JOIN sys.dm_xe_sessions s ON s.address = e.event_session_address
WHERE s.name = 'CollectWaitStatistics'
GO

And the output is just empty. Cool, huh? But what’s the purpose of an Event-Less Extended Event Session?

The answer is quite simple: you are not tracking any event anymore, and therefore your Extended Event Session doesn’t generate any overhead in SQL Server anymore. Of course you could also say that you can achieve the same by stopping the Extended Event Session. That’s true, but if your Extended Event Session was configured with an In-Memory target, like the ring_buffer target, you will also immediately loose that captured information!

And to overcome that specific problem, you can just drop events from an Extended Event Session without stopping the Event Session itself. And afterwards you can analyze the captured information in an In-Memory target. Cool, huh?

Summary

As you have seen, an Event-Less Extended Event Session makes sense when you deal with an In-Memory target, like the ring_buffer target, and you want to generate as less overhead as possible during the analysis of the captured information.

If you want to learn more about Extended Events, and how to use that technology to troubleshoot SQL Server problems, you can also sign-up for my one-day webinar on June 21. Hurry, because the remaining space is limited!

Thanks for your time,

-Klaus