Extended Events - 2

 Event Retention  

What should happen if the memory buffer ever fills up is also a configurable session option, known as Event Retention. Three options determine how SQL Server should respond when the memory buffer is full and a session tries to write event data to it:  

➤ no event loss — This option is the strictest of the possible settings and is not recommended for production environments. This setting means if the memory buffer ever fills up, end user queries must wait until there is free space in the memory buffer for their event data to be written to before they are allowed to continue. It’s very similar to how SQL Trace works in that event data is never lost, but the consequences of guaranteeing that complete set of event data on a busy system can be disastrous.  

➤ single event loss — This is the default when you create an Extended Events session. It instructs SQL Server that losing individual events is acceptable if it means the end user’s query performance won’t be affected like it would be with the no event loss option. Because the memory buffer is a FIFO list, the oldest event in the buffer is deleted to make room for the latest system activity. Although events are removed one at a time, if the Extended Events engine needs to write 500 new events to the buffer, it may have to delete the 500 oldest events first. While this sounds like a good compromise, it won’t protect you from having an undersized memory buffer on a busy system, as ultimately the amount of data you lose will render your captured data worthless.  

➤ multiple event loss — This tells SQL Server to favor protecting query performance at the expense of capturing event data on a massive scale. If the memory buffer is overwhelmed by incoming events, it can decide to completely empty the buffer rather than delete events one by one to maintain query performance. While this might mean you lose your event data, it’s a good fail-safe option to have in place if you ever deploy a session on a busy server for the first time and not know what its event generation rate is likely to be. 

 

Event Counter  

The event counter is one of three targets that don’t store any of the event data sent to them but instead perform a task with it. The event counter target does what its name suggests; it counts the number of times the event occurs. Unlike some other targets, however, it doesn’t require any event data or actions to be sent to it; the fact that the event has occurred is all it requires to increment the counter value it’s maintaining. In order to provide an accurate event count, access to update it is serialized, so it has to be used as a synchronous target. This shouldn’t cause you any performance issues though, as it’s a very lightweight target that requires very little data to be sent to it. Event counters, like the ring buffer, are in-memory structures. Therefore, their content is never written to an event file and is lost when SQL Server shuts down. 

 

 

For example, a session that captures all the logout events is shown here as a T-SQL create script:  

 

CREATE EVENT SESSION [LogoutEvents] ON SERVER ADD EVENT sqlserver.logout(ACTION(sqlserver.client_app_name, sqlserver.client_hostname,sqlserver.nt_username))  

ADD TARGET package0.event_file(SET filename=N'C:\XELogs\LogoutEvents.xel') WITH (STARTUP_STATE=ON)  

GO