What is an Event-Less Extended Event Session?

, 2018-06-20 (first published: )

(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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads