Databases Reference
In-Depth Information
That example should explain why you might choose to coni gure your session to use more memory,
based upon your preference for what should happen if the buffer ever i lls up, and therefore, how
much event data you're willing to lose in order to maintain system performance for users. Exactly
what events are dropped is determined by the Event Retention option.
Event Retention
What should happen if the memory buffer ever i lls up is also a coni gurable 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 recom-
mended for production environments. This setting means if the memory buffer ever i lls
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 i rst. 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
i rst time and not know what its event generation rate is likely to be.
Maximum Dispatch Latency
Now that you've seen how crucial it is to have adequate space in the memory buffer used by the
Extended Events engine, you can understand why there are various options that determine what
should happen if it i lls up. Of course, the best solution is to ensure that it never i lls up. There are
two options for this: setting a very large memory buffer size or coni guring the maximum dispatch
latency.
If an asynchronous target is being used, it's possible that events aren't transferred from the memory
buffer to the target until either the session stops and l ushes the buffer, or there is memory buffer
pressure that forces a l ush. This shouldn't usually be a problem, but you might think it's good to
keep as much free space in the memory buffer as possible in case of sudden system activity that gen-
erates a lot of event data.
Search WWH ::
Custom Search