Extended Event
SQL Extended events are used to generate event data, process that data, correlate it with system events if necessary, and send it to a target. (https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/sql-server-extended-events-packages?view=sql-server-ver15)
The types of use cases that Extended Events supports:
1. System health information, including session_id and sql_text for sessions with a severity greater than or equal to 20 or experiencing a memory-related error, non-yielding scheduler problems, deadlocks, long latch and lock waits, connectivity and security errors, and more. For complete details, see https://msdn.microsoft.com/en-us/library/ff877955.aspx.
2. Query performance diagnostics. Finding historical deadlocks or queries that did notend, troubleshooting waits for a particular session or query, capturing queries that match aspecific pattern, and getting query plans, to name a few. You can count the number ofoccurrences of a specific event to determine if a problem is recurring frequently.
3. Resource utilization monitoring. Creating a session to capture information about server resources, such as CPU, IO or memory utilization. Filtering events for specific utilization thresholds to fine-tune the diagnostic process. Correlating SQL Server events with Windows Event Tracing for Windows (ETW) logs that capture details about operating system activities.
4. Security audits. Capturing login failures by filtering events with Severity 14 and Error18456 with client application name to find malicious login attempts.
Events in Extended Events correspond to events in SQL Trace, but many more are supported in Extended Events to give you better diagnostic capabilities.
1. A package is the top-level container for the various types of Extended Events objects: events, targets, actions, types, predicates, and maps. Of all the available packages, you can only use the following three packages in an event session:
package0 Contains Extended Events system objects
sqlserver Contains objects related to SQL Server
sqlos Contains SQL Server Operating System (SQLOS) related objects objects
2. A target is the destination for the data collected about an event. For short-term diagnostics,you can use a memory-resident target. To persist the data, you can use the event_file target. After you create an Extended Event session, you canadd one or more targets, like this:
ADD TARGET package0.event_file(SETfilename=N'C:\[File Location]', max_file_size=(5),max_rollover_files=(4)),
ADD TARGET package0.ring_buffer
3. An action is a response or series of responses that you bind to an event. For example, you can use an action to detect an execution plan or calculate run-time statistics. Or you can add information from the global state, such as session_id, to the firing event to aid in troubleshooting, like this: ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.session_id, sqlserver.sql_text)
)
4. A session is the equivalent of a trace. When you create a session, youadd an event and actions to fire with that event, define one or more targets for the datacollected about the event, and optionally create predicates that define filters for the event.
CREATE EVENT SESSION [stored_proc] ON SERVER ADD EVENT sqlserver.sp_statement_completed
( ACTION (sqlserver.session_id, sqlserver.sql_text)
) ADD TARGET package0.event_file(SET filename=N'C:\[File Location]',max_file_size=(5),max_rollover_files=(4)), ADD TARGET package0.ring_buffer;
More about targets. An Extended Event target receives information about an event.
etw_classic_sync_target. Unlike the other targets that receive data asynchronously, this is an ETW target that receives data synchronously. You use it to monitor system activity.
event_counter This target counts the number of times that a specific event occurred.
event_file This target writes the event session output to a file on disk in binary format. You use the sys.fn_xe_file_target_read_file function to read the contents of the file.
histogram Like the event_counter target, the histogram target counts the occurrences of an event, but can count occurrences for multiple items separately and for both event fields or actions.
pair_matching This target helps you find start events that do not have a corresponding end event. For example, you can discover when a lock_acquired event occurred without a matching lock_released event within a reasonable time.
ring_buffer This target holds data in memory using an first-in, first-out method in which the oldest data is removed when the memory allocation is reached.
So it is worth noting that in the previous example, two targets: event_file and ring_buffer are added after Package0, which is specific for Extended Events system objects.
Example 1:
Answer:
Example 2:
Answer:
Example 3:
Answer:
Example 4:
You must troubleshoot performance issues that users report. You identify the following representative user sessions:
You need to configure the appropriate Extended Events. Which target should you use for each session? To answer, drag the appropriate Extended Event targets to the correct sessions. Each Extended Event target may be used once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Answer:
Session 1: Event Files - Event files use to write event session output from complete memory buffers to disk. This is an asynchronous target. Session 2: Histogram - Histogram use to count the number of times that a specified event occurs, based on a specified event column or action. This is an asynchronous target.
Incorrect Answers: ETW: The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) to correlate the event data with operating system or application event data.
Event Counter: An event counter counts all specified events that occur during an Extended Events session. Use to obtain information about workload characteristics without adding the overhead of full event collection.
References: https://docs.microsoft.com/en-us/sql/database-engine/sql-server-extended-events-targets?
This ETW ( etw_classic_sync_target target ) target processes synchronously the data it receives, whereas most targets process asynchronously. Note: Azure SQL Database does not support the etw_classic_sync_target target.
Unlike most targets, the event_counter target processes synchronously the data it receives. Note also that event_counter counts one event a time, histogram can count multiple events.
The database engine will disconnect from any target which is too slow and which thereby threatens to slow the performance of the database engine. This is one reason why most targets process asynchronously.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/targets-for-extended-events-in-sql-server?view=sql-server-ver15