Extended Events not collecting data.

Question

Extended Events not collecting data.

chrisrdba 471

Greetings. I have EE's running in an Azure SQL DB, collecting data to a ring buffer w success on a low usage environment. It collects data fine for a couple days then simply stops collecting. The only way I've found is to get it collecting data again is to stop/ start the whole thing.

A couple fun facts:

It doesn't actually stop until I stop it -- it's still running but just not collecting data.
I have the option checked to start on startup.
According to various articles I've read entries to the ring buffer are FIFO, so if it gets full it should simply wipe out old entries to make room for new entries.

Any ideas?

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-05-20T21:12:29.223+00:00

Not that I am sure that I can help, but which event(s) are you are collecting?
chrisrdba 471 Reputation points

2021-05-20T21:54:22.473+00:00

CREATE EVENT SESSION [longRunningQueries] ON DATABASE
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>=(25000000))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1),collect_statement=(1)
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>=(25000000))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>=(25000000)))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

1 answer

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-05-20T21:12:29.223+00:00

Not that I am sure that I can help, but which event(s) are you are collecting?
chrisrdba 471 Reputation points

2021-05-20T21:54:22.473+00:00

CREATE EVENT SESSION [longRunningQueries] ON DATABASE
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>=(25000000))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1),collect_statement=(1)
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>=(25000000))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([duration]>=(25000000)))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Answer 1

@chrisrdba Thank you for reaching out.

You have to specify the EVENT_RETENTION_MODE while creating the event session so that SQL Server can understand which events can be dropped when the ring buffer(s) are full.

These are the allowed retentions modes.

ALLOW_SINGLE_EVENT_LOSS An event can be lost from the session. A single event is only dropped when all the event buffers are full. Losing a single event when event buffers are full allows for acceptable SQL Server performance characteristics, while minimizing the loss of data in the processed event stream.

ALLOW_MULTIPLE_EVENT_LOSS Full event buffers containing multiple events can be lost from the session. The number of events lost is dependent upon the memory size allocated to the session, the partitioning of the memory, and the size of the events in the buffer. This option minimizes performance impact on the server when event buffers are quickly filled, but large numbers of events can be lost from the session.

NO_EVENT_LOSS No event loss is allowed. This option ensures that all events raised will be retained. Using this option forces all tasks that fire events to wait until space is available in an event buffer. This may cause detectable performance issues while the event session is active. User connections may stall while waiting for events to be flushed from the buffer.

More details are here

Please let us know if you have any further questions.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.

chrisrdba 471 Reputation points

2021-05-18T15:19:40.963+00:00

This is my current setting:

EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
KalyanChanumolu-MSFT 8,351 Reputation points

2021-05-20T10:42:16.42+00:00

While I cannot troubleshoot this remotely, how long do you need these events to persist?
Can you afford changing the retention to ALLOW_MULTIPLE_EVENT_LOSS and check if that helps?
chrisrdba 471 Reputation points

2021-05-20T21:05:37.053+00:00

I'd like them to persist as long as possible. I'm consistently getting < 100 records before it stops, so not sure changing that setting is going to leave it in a desirable state, even if it works.

Share via

Extended Events not collecting data.

1 answer

Your answer