Databases Reference
In-Depth Information
Target s
A
target
is the destination for all the information you can capture with Extended Events. However,
whereas the terminology used so far has been fairly straightforward, now you start to see some
terms that are less self-explanatory. In fact, it may be these terms you've seen in the past that made
you think twice about using the feature. Fortunately, the SSMS interface hides some of the complex-
ity from us.
Event Files
Event i les, like their name suggests, are l at i les that reside on an operating system drive and to
which event data is written. They remain there until you delete them manually. The event i les
themselves have a default i le extension of .
xel
and store data in a binary format that isn't human-
readable without a tool such as SQL Server Management Studio. Fortunately, SSMS can open these
i les even if they were captured on another server. It can also export the data to a database table or a
CSV i le, making the data more portable if needed.
When an Extended Events session is coni gured to use an event i le as a target, the event data is i rst
written by the Extended Events engine to memory buffers before a second process writes it from
memory to disk. This separation of processes is an important difference between Extended Events
and other troubleshooting tools, as it prevents the physical writing of the data to disk from affecting
performance for end users under normal circumstances. This behavior explains why event i les are
considered asynchronous targets.
Because Extended Events has been designed for use on servers with even the heaviest of workloads,
there are several options you can coni gure regarding how the data is buffered and what should
occur when the buffer is overwhelmed. The next three settings reviewed, memory buffer size, event
retention, and maximum dispatch latency, apply to most of the targets Extended Events sessions can
use, but it is useful to consider them now that you understand the potential problems a session with
a full memory buffer and an event i le target could have.
It's worth mentioning that the path and i lename you specify when you create a session must exist
otherwise the session won't start. It's often useful to create your own directory for Extended Events
i les, and then make sure SQL Server can read and write to it.
Memory Buf er Size
The size of the memory buffer is a coni gurable session option that can ultimately determine how
much of a performance impact an Extended Events session has on your server, and potentially
how much event data you might lose in busy conditions. For a small server with a small workload,
the default maximum buffer size of 4MB is probably sufi cient; but on a much busier server, you
might be writing more event data to the memory buffer than your target can subsequently l ush to
its storage during peak periods.
As a hypothetical example, if your Extended Events session is capturing event data at the rate of
200MB/sec, but your server can only write 100MB/sec to disk, you're very quickly going to either
need a lot of memory to store events before they're l ushed to disk or begin discarding data from
the buffer. The buffer is a First in, First out (FIFO) list, so if unl ushed events need to be deleted to
make room for newer events, then the oldest ones will be removed i rst.
Search WWH ::
Custom Search