Edit

Share via

Extended Events sessions

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

An Extended Events session is created in the SQL Server Database Engine process hosting the Extended Events engine. The following aspects of an Extended Events session provide a context for understanding the Extended Events infrastructure and the processing that takes place:

  • Session states. The different states that an Extended Events session is in when CREATE EVENT SESSION and ALTER EVENT SESSION statements are executed.

  • Session content and characteristics. The content of an Extended Events session, such as targets and events, and how these objects are related in a session or among sessions.

Session states

The following illustration shows the various states of an Extended Events session.

Diagram showing Extended Events session state.

Referring to the preceding figure, observe that session state changes as the different data definition language (DDL) commands are issued for an event session. The following table describes these changes in state.

Illustration label DDL statement Description
Create CREATE EVENT SESSION The host process creates a session object that contains the metadata provided by CREATE EVENT SESSION. The host process validates the session definition, validates the user permission level, and stores the metadata in the master database. At this point, the session isn't active.
Alter ALTER EVENT SESSION, STATE=START The host process starts the session. The host process reads the stored metadata, validates the session definition, verifies the level of user permission level, and creates the session. Session objects, such as events and targets, are loaded and event handling is active.
Alter ALTER EVENT SESSION, STATE=STOP The host process stops the active session but retains the metadata.
Drop DROP EVENT SESSION Depending on whether or not the session is active, Drop (DROP SESSION) deletes the metadata and closes the active session, or deletes the session metadata.

Session content and characteristics

Extended Events sessions have implied boundaries in that the configuration of one session doesn't change the configuration of another session. However, these boundaries don't prevent an event or a target type from being used in more than one session.

The following illustration shows session content and the relationship between packages and sessions.

Diagram showing object coexistence and sharing in sessions.

Referring to the preceding illustration, keep in mind that:

  • The mapping between package objects and sessions is many to many, which means that an object of a particular type can appear in several sessions, and a session can contain several objects.
  • The same event (Event 1) or target type (Target 1) can be used in more than one session.

Sessions have the following characteristics:

  • Actions and predicates are bound to events on a per-session basis. If you have Event 1 in Session A with Action 1 and Predicate Z, this doesn't in any way affect having Event 1 in Session B with Action 2 and Action 3 with no predicate.
  • Policies are attached to sessions to handle buffering and dispatch, and causality tracking.

Buffering refers to how event data is stored while an event session is running. Buffering policies specify how much memory to use for event data, and the loss policy for the events. Dispatch refers to the duration of time events stay in buffers before being served to targets for processing.

Causality tracking tracks work across multiple tasks. When causality tracking is enabled, each event fired has a unique activity ID across the system. The activity ID is a combination of a GUID value that remains constant across all events for a task, and a sequence number that is incremented each time an event is fired. When one task causes work to be done on another, the activity ID of the parent is sent to the child task. The child task outputs the parent's activity ID the first time it fires an event.

Time-bound event sessions

Starting with SQL Server 2025 (17.x) Preview, you can create an event session that stops automatically after the specified time elapses. This helps avoid situations where sessions might be left running indefinitely by mistake, consuming resources and potentially generating a large amount of data.

When the event data produced by a session is voluminous, time-bound event sessions help you capture smaller, targeted diagnostic data for specific durations of time. You can start a time-bound event session manually or using a scheduled job at the time of your choice and with a guarantee that the session won't be left running indefinitely.

To make an event session time-bound, specify the MAX_DURATION argument when creating or modifying the session. For more information, see CREATE EVENT SESSION and ALTER EVENT SESSION.

Just like any event session, you can stop a time-bound session before its maximum duration time elapses using the ALTER EVENT SESSION ... STATE = STOP statement. If the session is started again, the entire time duration specified by MAX_DURATION must elapse again before the session is stopped automatically.

You can also modify an existing event session using ALTER EVENT SESSION and either specify a different maximum duration time, or remove it by specifying MAX_DURATION = UNLIMITED. To modify the MAX_DURATION setting, the session must be stopped.

If an event session is time-bound, the max_duration column the in sys.server_event_sessions catalog view shows the maximum duration of the session in seconds. The event session has unlimited duration if the value is zero.

Related content