This ETW target processes the data it receives synchronously, whereas most targets process asynchronously.
Note
Azure SQL Managed Instance and Azure SQL Database don't support the etw_classic_sync_target target. As an alternative, use the event_file target with blobs stored in Azure Storage.
event_counter target
The event_counter target counts how many times each specified event occurs.
Unlike most other targets:
The event_counter target has no parameters.
The event_counter target processes the data it receives synchronously.
Example output captured by the event_counter target
Next is the CREATE EVENT SESSION statement that returned the previous results. For this example, the package0.counter field was used in the WHERE clause predicate to stop counting after the count reaches 4.
CREATE EVENT SESSION [event_counter_1]
ON SERVER
ADD EVENT sqlserver.checkpoint_begin -- Test by issuing CHECKPOINT; statements.
(
WHERE [package0].[counter] <= 4 -- A predicate filter.
)
ADD TARGET package0.event_counter
WITH
(
MAX_MEMORY = 4096 KB,
MAX_DISPATCH_LATENCY = 3 SECONDS
);
event_file target
The event_file target writes event session output from buffer to a disk file or to a blob in Azure Storage:
You specify the filename parameter in the ADD TARGET clause. The file extension must be xel.
The file name you choose is used by the system as a prefix to which a date-time based long integer is appended, followed by the xel extension.
Note
Azure SQL Managed Instance and Azure SQL Database only blobs in Azure Storage as the value of the filename parameter.
Here's an example of the CREATE EVENT SESSION with an ADD TARGET clause that adds an event_file target.
CREATE EVENT SESSION [locks_acq_rel_eventfile_22]
ON SERVER
ADD EVENT sqlserver.lock_acquired
(
SET
collect_database_name=(1),
collect_resource_description=(1)
ACTION (sqlserver.sql_text,sqlserver.transaction_id)
WHERE
(
[database_name]=N'InMemTest2'
AND
[object_id]=370100359
)
),
ADD EVENT sqlserver.lock_released
(
SET
collect_database_name=1,
collect_resource_description=1
ACTION(sqlserver.sql_text,sqlserver.transaction_id)
WHERE
(
[database_name]=N'InMemTest2'
AND
[object_id]=370100359
)
)
ADD TARGET package0.event_counter,
ADD TARGET package0.event_file
(
SET filename=N'C:\temp\locks_acq_rel_eventfile_22-.xel'
)
WITH
(
MAX_MEMORY=4096 KB,
MAX_DISPATCH_LATENCY=10 SECONDS
);
sys.fn_xe_file_target_read_file() function
The event_file target stores the data it receives in a binary format that's not human readable. The sys.fn_xe_file_target_read_file function lets you represent the contents of an xel file as a relational rowset.
For SQL Server 2016 and later versions, use a SELECT statement similar to the following example.
SELECT f.*
--,CAST(f.event_data AS XML) AS [Event-Data-Cast-To-XML] -- Optional
FROM sys.fn_xe_file_target_read_file(
'C:\temp\locks_acq_rel_eventfile_22-*.xel', NULL, NULL, NULL) AS f;
For SQL Server 2014, use a SELECT statement similar to the following example. After SQL Server 2014, the xem files are no longer used.
SELECT f.*
--,CAST(f.event_data AS XML) AS [Event-Data-Cast-To-XML] -- Optional
FROM sys.fn_xe_file_target_read_file(
'C:\temp\locks_acq_rel_eventfile_22-*.xel', 'C:\temp\metafile.xem', NULL, NULL) AS f;
In both of these examples, the * wildcard is used to read all xel files that start with the specified prefix.
In Azure SQL Database, you can call the sys.fn_xe_file_target_read_file() function after you create a database-scoped credential containing a SAS token with the Read and List permissions on the container with the xel blobs:
/*
Create a master key to protect the secret of the credential
*/
IF NOT EXISTS (
SELECT 1
FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY;
/*
(Re-)create a database scoped credential.
The name of the credential must match the URI of the blob container.
*/
IF EXISTS (
SELECT *
FROM sys.database_credentials
WHERE name = 'https://exampleaccount4xe.blob.core.windows.net/extended-events-container'
)
DROP DATABASE SCOPED CREDENTIAL [https://exampleaccount4xe.blob.core.windows.net/extended-events-container];
/*
The secret is the SAS token for the container. The Read and List permissions are set.
*/
CREATE DATABASE SCOPED CREDENTIAL [https://exampleaccount4xe.blob.core.windows.net/extended-events-container]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sp=rl&st=2023-10-09T22:12:54Z&se=2023-10-10T06:12:54Z&spr=https&sv=2022-11-02&sr=c&sig=REDACTED';
/*
Return event session data
*/
SELECT f.*
--,CAST(f.event_data AS XML) AS [Event-Data-Cast-To-XML] -- Optional
FROM sys.fn_xe_file_target_read_file('https://exampleaccount4xe.blob.core.windows.net/extended-events-container/event-session-1', DEFAULT, DEFAULT, DEFAULT) AS f;
In Azure SQL Managed Instance, you can call the sys.fn_xe_file_target_read_file() function after you create a server credential containing a SAS token with the Read and List permissions on the container with the xel blobs:
IF NOT EXISTS (
SELECT 1
FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'REDACTED';
/*
(Re-)create a database scoped credential.
The name of the credential must match the URI of the blob container.
*/
IF EXISTS (
SELECT *
FROM sys.credentials
WHERE name = 'https://exampleaccount4xe.blob.core.windows.net/extended-events-container'
)
DROP CREDENTIAL [https://exampleaccount4xe.blob.core.windows.net/extended-events-container];
/*
The secret is the SAS token for the container. The Read and List permissions are set.
*/
CREATE CREDENTIAL [https://exampleaccount4xe.blob.core.windows.net/extended-events-container]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sp=rl&st=2023-10-09T22:12:54Z&se=2023-10-10T06:12:54Z&spr=https&sv=2022-11-02&sr=c&sig=REDACTED';
/*
Return event session data
*/
SELECT f.*
--,CAST(f.event_data AS XML) AS [Event-Data-Cast-To-XML] -- Optional
FROM sys.fn_xe_file_target_read_file('https://exampleaccount4xe.blob.core.windows.net/extended-events-container/event-session-1', DEFAULT, DEFAULT, DEFAULT) AS f;
Tip
If you specify a blob name prefix instead of the full blob name in the first argument of sys.fn_xe_file_target_read_file(), the function will return data from all blobs in the container that match the prefix. This lets you retrieve data from all rollover files of a given event session without using the * wildcard, which isn't supported by Azure Storage.
The previous Azure SQL examples omit the xel extension to read all rollover files for a session named event-session-1.
Data stored in the event_file target
This is an example of data returned from sys.fn_xe_file_target_read_file in SQL Server 2016 (13.x) and later versions.
The histogram target processes the data it receives synchronously.
The source_type parameter is the key to controlling the histogram target:
source_type=0: collect data for an event field.
source_type=1: collect data for an action. This is the default.
The slots parameter default is 256. If you assign another value, the value is rounded up to the next power of 2. For example, slots=59 would be rounded up to 64. The maximum number of histogram slots for a histogram target is 16384.
When using histogram as the target, you might sometimes see unexpected results. Some events might not appear in the expected slots, while other slots might show a higher than expected count of events.
This might happen if a hash collision occurs when assigning events to slots. While this is rare, if a hash collision occurs, an event that should be counted in one slot is counted in another. For this reason, care should be taken assuming that an event didn't occur just because the count in a particular slot shows as zero.
As an example, consider the following scenario:
You set up an Extended Events session, using histogram as the target and grouping by object_id, to collect stored procedure execution.
You execute the Stored Procedure A. Then, you execute Stored Procedure B.
If the hash function returns the same value for the object_id of both stored procedures, the histogram shows Stored Procedure A being executed twice, and Stored Procedure B doesn't appear.
To mitigate this problem when the number of distinct values is relatively small, set the number of histogram slots higher than the square of expected distinct values. For example, if the histogram target has its source set to the table_name event field, and there are 20 tables in the database, then 20*20 = 400. The next power of 2 greater than 400 is 512, which is the recommended number of slots in this example.
Histogram target with an action
In its ADD TARGET ... (SET ...) clause, the following CREATE EVENT SESSION statement specifies the target parameter assignment source_type=1. This means that the histogram target tracks an action.
In the present example, the ADD EVENT ... (ACTION ...) clause happens to offer only one action to choose, namely sqlos.system_thread_id. In the ADD TARGET ... (SET ...) clause, we see the assignment source=N'sqlos.system_thread_id'.
Note
It isn't possible to add more than one target of the same type per event session. This includes the histogram target. It's also not possible to have more than one source (action / event field) per histogram target. Therefore, a new event session is required to track any additional actions or event fields in a separate histogram target.
CREATE EVENT SESSION [histogram_lockacquired]
ON SERVER
ADD EVENT sqlserver.lock_acquired
(
ACTION
(
sqlos.system_thread_id
)
)
ADD TARGET package0.histogram
(
SET
filtering_event_name=N'sqlserver.lock_acquired',
slots=16,
source=N'sqlos.system_thread_id',
source_type=1
);
The following data was captured. The values in the value column are system_thread_id values. For instance, a total of 236 locks were taken under thread 6540.
The C.3SELECT statement can find the actions that the system has available for you to specify in the CREATE EVENT SESSION statement. In the WHERE clause, you would first edit the o.name LIKE filter to match the actions that interest you.
Next is a sample rowset returned by the C.3 SELECT. The system_thread_id action is seen in the second row.
Package-Name Action-Name Action-Description
------------ ----------- ------------------
package0 collect_current_thread_id Collect the current Windows thread ID
sqlos system_thread_id Collect current system thread ID
sqlserver create_dump_all_threads Create mini dump including all threads
sqlserver create_dump_single_thread Create mini dump for the current thread
Histogram target with an event field
The following example sets source_type=0. The value assigned to source is an event field.
CREATE EVENT SESSION [histogram_checkpoint_dbid]
ON SERVER
ADD EVENT sqlserver.checkpoint_begin
ADD TARGET package0.histogram
(
SET
filtering_event_name = N'sqlserver.checkpoint_begin',
source = N'database_id',
source_type = 0
);
The following data was captured by the histogram target. The data shows that the database with ID 5 experienced 7 checkpoint_begin events.
value count
----- -----
5 7
7 4
6 3
SELECT to discover available fields on your chosen event
The C.4SELECT statement shows event fields that you can choose from. You would first edit the o.name LIKE filter to be your chosen event name.
The following rowset was returned by the C.4 SELECT. The rowset shows that database_id is the only field on the checkpoint_begin event that can supply values for the histogram target.
The pair_matching target enables you to detect start events that occur without a corresponding end event. For instance, it might be a problem when a lock_acquired event occurs but no matching lock_released event follows in a timely manner.
The system doesn't automatically match start and end events. Instead, you explain the matching to the system in your CREATE EVENT SESSION statement. When a start and end event are matched, the pair is discarded to focus on the unmatched start events.
Find matchable fields for the start and end event pair
By using the C.4 SELECT, we see in the following rowset there are about 16 fields for the lock_acquired event. The rowset displayed here has been manually split to show which fields our example matched on. For some fields such as duration, attempting to match is meaningless.
Package-Name Event-Name Field-Name Field-Description
------------ ---------- ---------- -----------------
sqlserver lock_acquired database_name NULL
sqlserver lock_acquired mode NULL
sqlserver lock_acquired resource_0 The ID of the locked object, when lock_resource_type is OBJECT.
sqlserver lock_acquired resource_1 NULL
sqlserver lock_acquired resource_2 The ID of the lock partition, when lock_resource_type is OBJECT, and resource_1 is 0.
sqlserver lock_acquired transaction_id NULL
sqlserver lock_acquired associated_object_id The ID of the object that requested the lock that was acquired.
sqlserver lock_acquired database_id NULL
sqlserver lock_acquired duration The time (in microseconds) between when the lock was requested and when it was canceled.
sqlserver lock_acquired lockspace_nest_id NULL
sqlserver lock_acquired lockspace_sub_id NULL
sqlserver lock_acquired lockspace_workspace_id NULL
sqlserver lock_acquired object_id The ID of the locked object, when lock_resource_type is OBJECT. For other lock resource types it will be 0
sqlserver lock_acquired owner_type NULL
sqlserver lock_acquired resource_description The description of the lock resource. The description depends on the type of lock. This is the same value as the resource_description column in the sys.dm_tran_locks view.
sqlserver lock_acquired resource_type NULL
An example of the pair_matching target
The following CREATE EVENT SESSION statement specifies two events, and two targets. The pair_matching target specifies two sets of fields to match the events into pairs. The sequence of comma-delimited fields assigned to begin_matching_columns and end_matching_columns must be the same. No tabs or newlines are allowed between the fields mentioned in the comma-delimited value, although spaces are allowed.
To narrow the results, we first selected from sys.objects to find the object_id of our test table. We added a filter for that one object ID in the ADD EVENT ... (WHERE ...) clause.
CREATE EVENT SESSION [pair_matching_lock_a_r_33]
ON SERVER
ADD EVENT sqlserver.lock_acquired
(
SET
collect_database_name = 1,
collect_resource_description = 1
ACTION (sqlserver.transaction_id)
WHERE
(
[database_name] = 'InMemTest2'
AND
[object_id] = 370100359
)
),
ADD EVENT sqlserver.lock_released
(
SET
collect_database_name = 1,
collect_resource_description = 1
ACTION (sqlserver.transaction_id)
WHERE
(
[database_name] = 'InMemTest2'
AND
[object_id] = 370100359
)
)
ADD TARGET package0.event_counter,
ADD TARGET package0.pair_matching
(
SET
begin_event = N'sqlserver.lock_acquired',
begin_matching_columns =
N'resource_0, resource_1, resource_2, transaction_id, database_id',
end_event = N'sqlserver.lock_released',
end_matching_columns =
N'resource_0, resource_1, resource_2, transaction_id, database_id',
respond_to_memory_pressure = 1
)
WITH
(
MAX_MEMORY = 8192 KB,
MAX_DISPATCH_LATENCY = 15 SECONDS
);
To test the event session, we purposefully prevented two acquired locks from being released. We did this with the following T-SQL steps:
BEGIN TRANSACTION.
UPDATE MyTable....
Purposefully not issue a COMMIT TRANSACTION, until after we examined the targets.
Later after testing, we issued a COMMIT TRANSACTION.
The simple event_counter target provided the following output rows. Because 52-50=2, the output implies we see 2 unpaired lock_acquired events when we examine the output from the pair-matching target.
The pair_matching target provided the following output. As suggested by the event_counter output, we do indeed see the two lock_acquired rows. The fact that we see these rows at all means these two lock_acquired events are unpaired.
The rows for the unpaired lock_acquired events could include the T-SQL text provided by the sqlserver.sql_text action. This captures the query that acquired the locks.
ring_buffer target
The ring_buffer target is handy for a quick and simple event collection in memory only. When you stop the event session, the stored output is discarded.
In this section, we also show how you can use XQuery to convert the XML representation of the ring buffer contents into a more readable relational rowset.
Tip
When adding a ring_buffer target, set its MAX_MEMORY parameter to 1024 KB or less. Using larger values might increase memory consumption unnecessarily.
By default, MAX_MEMORY for a ring_buffer target isn't limited in SQL Server, and is limited to 32 MB in Azure SQL Database and Azure SQL Managed Instance.
You consume data from a ring_buffer target by converting it to XML, as shown in the following example. During this conversion, any data that doesn't fit into a 4-MB XML document is omitted. Therefore, even if you capture more events in the ring buffer by using larger MAX_MEMORY values (or by leaving this parameter at its default value), you might not be able to consume all of them because of the 4-MB limit on the XML document size, considering the overhead of XML markup and Unicode strings.
You know that the contents of the ring buffer are omitted during conversion to XML if the truncated attribute in the XML document is set to 1, for example:
Here's an example of creating an event session with a ring_buffer target. In this example, the MAX_MEMORY parameter appears twice: once to set the ring_buffer target memory to 1024 KB, and once to set the event session buffer memory to 2 MB.
CREATE EVENT SESSION [ring_buffer_lock_acquired_4]
ON SERVER
ADD EVENT sqlserver.lock_acquired
(
SET collect_resource_description=(1)
ACTION(sqlserver.database_name)
WHERE
(
[object_id]=(370100359) -- ID of MyTable
AND
sqlserver.database_name='InMemTest2'
)
)
ADD TARGET package0.ring_buffer
(
SET MAX_EVENTS_LIMIT = 98,
MAX_MEMORY = 1024
)
WITH
(
MAX_MEMORY = 2 MB,
MAX_DISPATCH_LATENCY = 3 SECONDS
);
XML output received for lock_acquired by the ring_buffer target
When retrieved by a SELECT statement, the content of a ring buffer is presented as an XML document. An example is shown next. However, for brevity, all but two <event> elements have been removed. Further, within each <event>, a handful of <data> elements have been removed as well.
To see the preceding XML, you can issue the following SELECT while the event session is active. The XML data is retrieved from the system view sys.dm_xe_session_targets.
SELECT CAST(LocksAcquired.TargetXml AS XML) AS RBufXml
INTO #XmlAsTable
FROM (
SELECT CAST(t.target_data AS XML) AS TargetXml
FROM sys.dm_xe_session_targets AS t
INNER JOIN sys.dm_xe_sessions AS s
ON s.address = t.event_session_address
WHERE t.target_name = 'ring_buffer'
AND s.name = 'ring_buffer_lock_acquired_4'
) AS LocksAcquired;
SELECT *
FROM #XmlAsTable;
XQuery to see the XML as a rowset
To see the preceding XML as a relational rowset, continue from the preceding SELECT statement by issuing the following T-SQL. The commented lines explain each use of XQuery.
SELECT
-- (A)
ObjectLocks.value('(@timestamp)[1]', 'datetime') AS [OccurredDtTm],
-- (B)
ObjectLocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(32)') AS [Mode],
-- (C)
ObjectLocks.value('(data[@name="transaction_id"]/value)[1]', 'bigint') AS [TxnId],
-- (D)
ObjectLocks.value('(action[@name="database_name" and @package="sqlserver"]/value)[1]', 'nvarchar(128)') AS [DatabaseName]
FROM #XmlAsTable
CROSS APPLY
-- (E)
TargetDateAsXml.nodes('/RingBufferTarget/event[@name="lock_acquired"]') AS T(ObjectLocks);
XQuery notes from preceding SELECT
(A)
timestamp= attribute's value, on <event> element.
The '(...)[1]' construct ensures only one value returned per iteration, as is a required limitation of the .value() XQuery method of XML data type variable and columns.
(B)
<text> element's inner value, within a <data> element, which has its name= attribute equal to mode.
(C)
<value> elements inner value, within a <data> element, which has its name= attribute equal to transaction_id.
(D)
<event> contains <action>.
<action> having name= attribute equal to database_name, and package= attribute equal to sqlserver (not package0), get the inner value of <value> element.
(E)
CROSS APPLY causes processing to repeat for every individual <event> element, which has its name attribute equal to lock_acquired.
This applies to the XML returned by the preceding FROM clause.
Output from XQuery SELECT
Next is the rowset generated by the preceding T-SQL, which includes XQuery.
The event_stream target can only be used in .NET programs written in languages like C#. C# and other .NET developers can access an event stream through .NET Framework classes in the Microsoft.SqlServer.XEvents.Linq namespace. This target can't be used in T-SQL.
If you encounter error 25726, The event data stream was disconnected because there were too many outstanding events. To avoid this error either remove events or actions from your session or add a more restrictive predicate filter to your session. when reading from the event_stream target, it means that the event stream filled up with data faster than the client could consume the data. This causes the Database Engine to disconnect from the event stream to avoid affecting Database Engine performance.
Use Extended Events with Event Tracing for Windows to monitor system activity. Learn about CREATE EVENT SESSION, ALTER EVENT SESSION, and DROP EVENT SESSION.
CREATE EVENT SESSION creates an Extended Events session that identifies the source of the events, the event session targets, and the event session options.
You can take actions in Object Explorer that affect Extended Events, such as create, start or stop, export, import, edit, or delete Extended Events sessions.
