--- PDF PAGE 706 ---
700
Packages
A package is a container for the objects used within Extended Events. Here are the four 
types of SQL Server package:
•	
Package0: The default package, used for Extended Events system 
objects.
•	
Sqlserver: Used for SQL Server–related objects.
•	
Sqlos: Used for SQLOS-related objects.
•	
SecAudit: Used by SQL Audit; however, its objects are not exposed.
Events
An event is an occurrence of interest that you can trace. It may be a SQL batch 
completing, a cache miss, or a page split, or virtually anything else that can happen 
within the Database Engine, depending on the nature of the trace that you are 
configuring. Each event is categorized by channel and keyword (also known as 
category). A channel is a high-level categorization, and all events in SQL Server 2019 fall 
into one of the channels described in Table 19-1.
Table 19-1.  Channels
Channel
Description
Admin
Well-known events with well-known resolutions. For example, deadlocks, server 
starts, CPU thresholds being exceeded, and the use of deprecated features.
Operational
Used for troubleshooting issues. For example, bad memory being detected, 
an AlwaysOn Availability Group replica changing its state, and a long IO being 
detected are all events that fall within the Operational channel.
Analytic
High-volume events that you can use for troubleshooting issues such as 
performance. For example, a transaction beginning, a lock being acquired, and a 
file read completing are all events that fall within the Analytic channel.
Debug
Used by developers to diagnose issues by returning internal data. The events in 
the Debug channel are subject to change in future versions of SQL Server, so you 
should avoid them when possible.
Chapter 19  Extended Events

--- PDF PAGE 707 ---
701
Keywords, also known as categories, are much more fine grain. There are 88 
categories within SQL Server 2019. These categories can be listed, by running the query 
in Listing 19-1.
Listing 19-1.  Return List of Categories
SELECT DISTINCT map_value AS Category
FROM sys.dm_xe_map_values map
WHERE map.name = 'keyword_map'
ORDER BY map.map_value
Targets
A target is the consumer of the events; essentially, it is the device to which the trace data 
will be written. The targets available within SQL Server 2019 are detailed in Table 19-2.
Table 19-2.  Targets
Target
Synchronous/Asynchronous
Description
Event counter
Synchronous
Counts the number of events that occur during a 
session
Event file
Asynchronous
Writes the event output to memory buffers and 
then flushes them to disk
Event pairing
Asynchronous
Determines if a paired event occurs without its 
matching event, e.g., if a statement started but 
never completed
ETW*
Synchronous
Used to correlate Extended Events with operating 
system data
Histogram
Asynchronous
Counts the number of events that occur during a 
session, based on an action or event column
Ring buffer
Asynchronous
Stores data in a memory buffer, using First In First 
Out (FIFO) methodology
*Event Tracking for Windows
Chapter 19  Extended Events

--- PDF PAGE 719 ---
713
On the Advanced page, we can specify the desired behavior in the event of memory 
pressure: whether single-event loss is acceptable, whether multiple-event loss is 
acceptable, or whether there should be no event loss at all. We can also set the minimum 
and maximum size for events and how memory partitioning should be applied. This 
is discussed in more detail in the following section. Additionally, we can configure 
dispatch latency. This indicates the maximum amount of time that an event remains in 
the buffers before it is flushed to disk.
Using T-SQL
You can also create event sessions via T-SQL using the CREATE EVENT SESSION DDL 
statement. The command accepts the arguments detailed in Table 19-3.
Figure 19-6.  The Data Storage page
Chapter 19  Extended Events

--- PDF PAGE 720 ---
714
The statement also accepts the WITH options, detailed in Table 19-4. The WITH 
statement is specified once, at the end of the CREATE EVENT SESSION statement.
Table 19-3.  Create Event Session Arguments
Argument
Description
event_session_name
The name of the event session that you are creating.
ADD EVENT | SET
Specified for every event that is added to the session, followed by the 
name of the event, in the format package.event. You can use the 
SET statement to set event-­specific customizations, such as including 
nonmandatory event fields.
ACTION
Specified after each ADD EVENT argument if global fields should be 
captured for that event.
WHERE
Specified after each ADD EVENT argument if the event should be 
filtered.
ADD TARGET | SET
Specified for each target that will be added to the session. You can use 
the SET statement to populate target-specific parameters, such as the 
filename parameter for the event_file target.
Table 19-4.  Create Event Session WITH Options
Option
Description
MAX_MEMORY
The maximum amount of memory that the event session can use 
for buffering events before dispatching them to the target(s).
EVENT_RETENTION_MODE
Specifies the behavior if the buffers become full. Acceptable values 
are ALLOW_SINGLE_EVENT_LOSS, which indicates that a single 
event can be dropped if all buffers are full; ALLOW_MULTIPLE_
EVENT_LOSS, which indicates that an entire buffer can be dropped 
if all buffers are full; and NO_EVENT_LOSS, which indicates that 
tasks that cause events to fire are to wait until there is space in the 
buffer.
(continued)
Chapter 19  Extended Events

--- PDF PAGE 720 ---
714
The statement also accepts the WITH options, detailed in Table 19-4. The WITH 
statement is specified once, at the end of the CREATE EVENT SESSION statement.
Table 19-3.  Create Event Session Arguments
Argument
Description
event_session_name
The name of the event session that you are creating.
ADD EVENT | SET
Specified for every event that is added to the session, followed by the 
name of the event, in the format package.event. You can use the 
SET statement to set event-­specific customizations, such as including 
nonmandatory event fields.
ACTION
Specified after each ADD EVENT argument if global fields should be 
captured for that event.
WHERE
Specified after each ADD EVENT argument if the event should be 
filtered.
ADD TARGET | SET
Specified for each target that will be added to the session. You can use 
the SET statement to populate target-specific parameters, such as the 
filename parameter for the event_file target.
Table 19-4.  Create Event Session WITH Options
Option
Description
MAX_MEMORY
The maximum amount of memory that the event session can use 
for buffering events before dispatching them to the target(s).
EVENT_RETENTION_MODE
Specifies the behavior if the buffers become full. Acceptable values 
are ALLOW_SINGLE_EVENT_LOSS, which indicates that a single 
event can be dropped if all buffers are full; ALLOW_MULTIPLE_
EVENT_LOSS, which indicates that an entire buffer can be dropped 
if all buffers are full; and NO_EVENT_LOSS, which indicates that 
tasks that cause events to fire are to wait until there is space in the 
buffer.
(continued)
Chapter 19  Extended Events

--- PDF PAGE 721 ---
715
Option
Description
MAX_DISPATCH_LATENCY
The maximum amount of time that events can reside in the session 
buffers before being flushed to the target(s), specified in seconds.
MAX_EVENT_SIZE
The maximum possible size for event data from any single event. 
It can be specified in kilobytes or megabytes and should only be 
configured to allow events that are larger than the MAX_MEMORY 
setting.
MEMORY_PARTITION_MODE
Specifies where vent buffers are created. Acceptable values are 
NONE, which indicates that the buffers will be created within the 
instance; PER_NODE, which indicates that the buffers will be 
created for each NUMA node; and PER_CPU, which means that 
buffers will be created for each CPU.
TRACK_CAUSALITY
Specifies that an additional GUID and sequence number will be 
stored with each event so that events can be correlated.
STARTUP_STATE
Specifies if the session automatically starts when the instance 
starts. ON indicates it does, OFF indicates it does not.
Table 19-4.  (continued)
Caution  Using the NO_EVENT_LOSS option for EVENT_RETENTION_MODE can 
cause performance issues on your instance, because tasks may have to wait to 
complete until there is space in the event session’s buffers to hold the event data.
The script in Listing 19-5 demonstrates how you can use T-SQL to create a session 
called LogFileIO. This session is similar to the Database Log File IO Tracking template, 
provided through the GUI. The difference is that we are additionally capturing the 
sqlos.wait_completed event and the sqlserver.DatabaseName global field, which we 
are also filtering on this, so that only details of the Chapter19 transaction log are tracked.
The results of the trace will be written to a file called LogFileIO.xel in the C:\Logs 
folder (which needs to be pre-created). The STARTUP_STATE option is used to start the 
session.
Chapter 19  Extended Events