Building blocks of Extended Events data collection

Extended Events are an excellent way to collect data about a SQL Server that provides a vast array of events that can be used for performance monitoring, troubleshooting, or auditing a server. In this article, I’ll explain the building blocks of Extended Events data collection.

While using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging.

This article walks through the steps to create, configure, and implement Extended Events in SQL Server, providing the prerequisite code and concepts to build an automated collection process.

The basics of collecting and reading extended events data

The basic steps to use Extended Events are as follows:

1. Create an Extended Events session
   1. Add events to the Extended Events session
   2. Add actions to the Extended Events session
   3. Add filters
   4. Add a target for event data
   5. Specify options
2. Start the Extended Events session
3. Periodically read data from the Extended Events session

Extended Events may be worked with via the SQL Server Management Studio GUI or using T-SQL. For this article, T-SQL will be used exclusively as it allows for code to be more easily shared and manipulated and is easier to copy and use than a sequence of images or videos.

Once data is read from Extended Events, it needs to be parsed and placed into a more permanent location for analytics, monitoring, and/or posterity.

The following is a brief review of creating and managing Extended Events sessions, followed by a more detailed process that automates collecting, extracting, and managing Extended Events processes and data. This is separated into distinct sections as each will become a modular script within the Extended Events solution crafted in this article.

Creating an Extended Events session

Creating a new Extended Events session via T-SQL involves deciding up-front how to configure and run it. Typically, filters, events, and options will be tweaked afterwards as the resulting data is reviewed and a better idea of how it should look is envisioned.

The following is the most bare-bones way to create an Extended Events session:

CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER

ADD EVENT sqlserver.rpc_completed;

This script creates an event session, names it, and adds a single event. All other Extended Event options will use their default settings The session’s existence can be confirmed via sys.server_event_sessions:

SELECT
*
FROM sys.server_event_sessions
WHERE server_event_sessions.name = 'Sql_Server_Query_Metrics';

The results return a row with some additional details:

Note the value of 0 provided by startup_state, which indicates that the Extended Events session exists but is stopped.

The view sys.dm_xe_sessions can also be used to check for the existence of active Extended Events sessions:

SELECT
*
FROM sys.dm_xe_sessions
WHERE dm_xe_sessions.name = 'Sql_Server_Query_Metrics';

This time, no results are returned:

The lack of rows returned is another way to confirm that this session is not started.

Creating an Extended Events session with no additional parameters is not a good idea. It is good practice to provide as much detail as possible about the target, events, actions, filters, and options.

Add events to the Extended Events session

An event is a response to a specific triggered occurrence in SQL Server. It could be a deadlock, a completed query, or a user login. All of the available events in SQL Server can be browsed via system views:

SELECT
*
FROM sys.dm_xe_packages;

This query returns some basic information about each Extended Events package:

This query returns a list of all events. Note that the view dm_xe_objects also contains types, targets, actions, and other entities, as given by the column object_type.

SELECT
dm_xe_packages.name AS package_name,
dm_xe_packages.description,
dm_xe_objects.name AS object_name,
dm_xe_objects.object_type,
dm_xe_objects.description
FROM sys.dm_xe_objects
INNER JOIN sys.dm_xe_packages
ON dm_xe_packages.guid = dm_xe_objects.package_guid
WHERE dm_xe_objects.object_type = 'event';

The results are extensive, but easy to filter:

At least one event is required for an Extended Events session, but multiple may be added like this:

CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER
ADD EVENT sqlserver.rpc_completed,
ADD EVENT sqlserver.sql_batch_completed;

This script allows for any number of events to be attached to an Extended Events session. Keep in mind that data for each event is written to the Extended Event target, regardless of whether it is needed or not. Therefore, it is worthwhile to only include needed events as extraneous events will slow down Extended Events, waste storage space, and slow down event processing.

Add actions to the Extended Events session

Several data points are automatically collected for each event type. Actions allow additional elements to be retrieved along with their respective event, thereby customizing events with data that does not need to be retrieved by default for all use-cases. Actions are added onto events like this:

CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER
ADD EVENT sqlserver.rpc_completed (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.session_id,
sqlserver.username)),
ADD EVENT sqlserver.sql_batch_completed (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.session_id,
sqlserver.username));

For the two events in this example, 5 additional fields are added to those events as actions and will be included in the result set. Only add actions that are needed as they take up space and computing resources to collect.

Add filters to the Extended Events session

Filters allow results to be limited to those required for the monitoring/troubleshooting task at hand. They remove extraneous results and decrease the size of the extended event payload data, which will make consumption of that data faster and more efficient.

Filters are added as WHERE clauses onto each event:

CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER
ADD EVENT sqlserver.rpc_completed (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.session_id,
sqlserver.username)
WHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%')),
ADD EVENT sqlserver.sql_batch_completed (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.session_id,
sqlserver.username)
WHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%'));

These WHERE clauses filter out all events that originate from SQL Server Agent by filtering on client_app_name. There are likely multiple ways to filter out a given set of events for any given Extended Events session. Frequently used are filters against client_app_name, database_name, client_hostname, or username. These allow the source of events to be filtered up-front before data is written to a target.

Add a target to the extended events session

Event data needs to be written somewhere, and that somewhere needs to have the resources to accept whatever volume of data will originate from a given Extended Events Session. A complete list of all target types is available here:
Targets for Extended Events in SQL Server – SQL Server | Microsoft Docs

While that list is long, most users of Extended Events will use either the ring buffer or a file as the target for their event output. The rest are worth reviewing to gain knowledge and to understand what else is available but are typically used for less common tasks.

The ring buffer stores events in memory and will automatically clear itself out as a memory cap is reached or if a maximum event count is reached. The ring buffer is great for testing Extended Events and developing new code, but is not ideal for production workloads, especially larger ones.

A ring buffer target can be specified like this:

CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER
ADD EVENT sqlserver.rpc_completed (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.session_id,
sqlserver.username)
WHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%')),
ADD EVENT sqlserver.sql_batch_completed (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.session_id,
sqlserver.username)
WHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%'))
ADD TARGET package0.ring_buffer (
SET max_memory = 2000, -- 2000kb
max_events_limit = (500));

Note that it is possible to specify multiple targets for an Extended Events session if there is a need to do so.

The most common target for event data is a file, which is stored in an XML format with a *.XEL file extension, by default. A file target allows for more customization of its size and usage and can accommodate far more data than the ring buffer. In addition, if the database server is restarted for any reason, the data present in a file will persist, whereas ring buffer data is stored in memory and will no longer be available.

A file target can be added using similar code:

CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER
ADD EVENT sqlserver.rpc_completed (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.session_id,
sqlserver.username)
WHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%')),
ADD EVENT sqlserver.sql_batch_completed (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.session_id,
sqlserver.username)
WHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%'))
ADD TARGET package0.event_file
(SET FILENAME = 'C:\SQLBackup\Sql_Server_Query_Metrics.xel',
MAX_FILE_SIZE = 1000, -- 1000MB
MAX_ROLLOVER_FILES = 3);

This command sets the target to a file on my local drive with a file size of 1000MB and up to 3 rollover files. Rollover files allow Extended Events files to be subdivided into smaller units, allowing for faster access and easier removal.

Specify options for an Extended Events session

The last step in configuring an Extended Events session is to set options that determine how events are captured, stored, and maintained. The following is an example of a few options that are added to the growing demonstration from above:

CREATE EVENT SESSION Sql_Server_Query_Metrics ON SERVER
ADD EVENT sqlserver.rpc_completed (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.session_id,
sqlserver.username)
WHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%')),
ADD EVENT sqlserver.sql_batch_completed (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.session_id,
sqlserver.username)
WHERE (sqlserver.client_app_name NOT LIKE 'SQLAgent%'))
ADD TARGET package0.event_file
(SET FILENAME = 'C:\SQLBackup\Sql_Server_Query_Metrics.xel',
MAX_FILE_SIZE = 1000, -- 1000MB
MAX_ROLLOVER_FILES = 3)
WITH ( EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 15 SECONDS,
MAX_MEMORY = 1024MB,
STARTUP_STATE = OFF);

The MAX_MEMORY setting determines the size of the memory buffer used to capture event data prior to writing it to the file target. The event retention mode allows for some data loss to occur in the event that the buffer fills up. This ensures that SQL Server’s performance is not negatively impacted by a sudden flood of events that need to be written to the target faster than SQL Server can do so. As always, rule #1 of monitoring is that the monitoring system should impact the underlying system as little as possible!

The latency setting similarly provides some buffer time for events to make their way to the target from memory. Typically, some latency is tolerable, and it ensures that events are not being written from memory to the target constantly. A latency of 0 or INFINITE means that events will only be written to the target when the buffer is full or the event session is closed.

Lastly, the startup state determines if the event session is started immediately upon creation, or if it is created in a stopped state.

There are a number of other settings that can be adjusted, such as whether to track causality, how to configure the memory partition mode, or set the event size cap. The Extended Events session creation documentation has good coverage of each of these options in greater detail than is in scope for this article:

CREATE EVENT SESSION (Transact-SQL) – SQL Server | Microsoft Docs

Start the Extended Events session

Assuming STARTUP_STATE = OFF when the event session is created, the session will need to be started. This can be done with the following T-SQL:

ALTER EVENT SESSION Sql_Server_Query_Metrics ON SERVER
STATE = START;

The event session may be stopped at any time with a similar script:

ALTER EVENT SESSION Sql_Server_Query_Metrics ON SERVER
STATE = STOP;

Read data from the Extended Events session file

The work thus far relies on the ability to quickly read data from an Extended Events session. This is by far the most challenging part of using Extended Events. The target data is written as XML and effectively making use of it requires XML parsing. SQL Server provides a function that can be used to read directly from this XML data called sys.fn_xe_file_target_read_file. This is how a query using this function will look:

SELECT
timestamp_utc AS sample_time_utc,
CAST(event_data AS VARCHAR(MAX)) AS event_data_xml
FROM sys.fn_xe_file_target_read_file(
'C:\SQLBackup\Sql_Server_Query_Metrics.xel', NULL, NULL, NULL);

The query returns nothing, though:

That was disappointing! The file name used for the file target will have additional numbers appended to it to allow it to differentiate files used for the event session. This is necessary if rollover files are used as multiple files cannot share the same name. Details about the file can be returned with a query against sys.server_event_session_fields:

SELECT
*
FROM sys.server_event_session_fields
INNER JOIN sys.server_event_sessions
ON server_event_sessions.event_session_id = 
server_event_session_fields.event_session_id
WHERE server_event_sessions.name = 'Sql_Server_Query_Metrics';

This query returns any file parameters for the event session target and can be filtered by joining back to sys.server_event_sessions:

Note that the file name provided here does not match the physical file name but the name provided when the event session was created. The physical file looks like this:

The function fn_xe_file_target_read_file can include wild cards, so there is no need to fish around for the exact physical file name:

SELECT
timestamp_utc AS sample_time_utc,
CAST(event_data AS VARCHAR(MAX)) AS event_data_xml
FROM sys.fn_xe_file_target_read_file(
'C:\SQLBackup\Sql_Server_Query_Metrics*.xel', NULL, NULL, NULL);

The results are as follows:

Retrieved are the timestamp and a big glob of XML. It is generally faster to store this data as XML and parse it asynchronously at a later time or as part of a structured process. XML shredding takes time and potentially hefty computing resources, so waiting on it is not ideal.

It is important to note that for exceptionally high event volume, the fastest way to process the XML is to use PowerShell or a .NET application to directly read the XEL file. Doing that here is well out of scope for this article but is an exercise undertaken by others in the community and worth investigating if all else fails.

Extended Events data collection

It is here that this work moves from building blocks into building. This article has provided an overview of creating, configuring, and pulling data from Extended Events sessions. To go further, permanent database objects will need to be created to manage and store the XML and the event data derived from it.

Extended Events are highly customizable and versatile. Therefore, efforts to build a process that uses the content discussed thus far will benefit from flexibility. There are many, many ways to solve this challenge, and each database environment will benefit from slightly different approaches.

There is a great benefit in using T-SQL exclusively to implement and manage Extended Events. While the SQL Server Management Studio UI is convenient, it is not a scalable approach for large numbers of database servers. Therefore, I encourage the reader to pursue using T-SQL, regardless of the simplicity or complexity of event collection needs.

The next article will combine the concepts presented here into a more permanent solution that can be tailored to a wide variety of SQL Server monitoring and data collection challenges.

If you liked this article, you might also like Collecting SQL Server performance monitor data – Simple Talk (red-gate.com).

The post Building blocks of Extended Events data collection appeared first on Simple Talk.

from Simple Talk https://ift.tt/3kalDhx
via