Yesterdays post, Querying the Extended Events Metadata, showed how to discover the objects available for use in Extended Events.  In today's post, we’ll take a look at the DDL Commands that are used to create and manage Event Sessions based on the objects available in the system.  Like other objects inside of SQL Server, there are three DDL commands that are used with Extended Events; CREATE EVENT SESSION, ALTER EVENT SESSION, and DROP EVENT SESSION.  The command names are self explanatory and their purposes should be clear to most SQL Server DBA’s.  The books online covers the syntax in detail so I won’t rehash all of that in this post, but will instead provide examples for each that cover specific areas of the commands.

CREATE EVENT SESSION

Creating an Event session adds the session definition to the Extended Events Engine making it available for event collection.  Creating an Event Session requires that at least one event be added to the session, but an Event Session does not need to have a target added to it to be created.  An individual event or target can only  be used once in an Event Session, and the complex predicates that are possible in Extended Events mitigates the need to have the same event multiple times in a session.  Session options are optional and if left unspecified the defaults documented in the books online will be used for the Event Session.  An example Event Session that shows all of the options used is:

	CREATE EVENT SESSION [TrackTempdbFileWrites] ON SERVER
	ADD EVENT sqlserver.file_write_completed(
	   SET collect_path = 1
	   ACTION (sqlserver.sql_text)
	   WHERE database_id = 2),
	ADD EVENT sqlserver.file_written(
	   WHERE database_id = 2)
	ADD TARGET package0.ring_buffer,
	ADD TARGET package0.asynchronous_bucketizer(
	     SET filtering_event_name='sqlserver.file_write_completed', source_type=0, source='file_id')
	WITH (MAX_MEMORY=4096 KB,
	     EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
	     MAX_DISPATCH_LATENCY=30 SECONDS,
	     MAX_EVENT_SIZE=0 KB,
	     MEMORY_PARTITION_MODE=NONE,
	     TRACK_CAUSALITY=OFF,
	     STARTUP_STATE=OFF)
	GO
	

This event session captures the sqlserver.file_write_completed event and specifies that the event should collect the path to the file, which is a customizable column on the event, to execute the sql_text action to collect the sql_text that caused the event to fire, if it is available, and to only fire the event for database_id = 2, which happens to be tempdb.  Notice that in the Event definition, only the ACTION specification is defined inside of a set of parenthesis, which is defined the the BOL as required in the <event_definition> specification:

<event_definition>::=
{
    ADD EVENT [event_module_guid].event_package_name.event_name
         [ ( {
                 [ SET { event_customizable_attribute = <value> [ ,...n] } ]
                 [ ACTION ( { [event_module_guid].event_package_name.action_name [ ,...n] } ) ]
                 [ WHERE <predicate_expression> ]
        } ) ]
}

Modifications to a specific event are enclosed in a set of parenthesis, and then only the Action is constrained to provide a set of parenthesis in its definition.  However, the <predicate_expression> can optionally include parenthetical notation to group predicate sets similar to what is possible in the TSQL WHERE clause.  The session also captures the file_written event when the database_id = 2.

The session utilizes two targets, not because it necessarily provides additional meaning to the event consumption, but because I needed a valid example that shows how multiple targets are defined on a single event session at creation.  The ring_buffer target is defined and will capture the raw event data using the default configuration options for the target, and the asynchronous_bucketizer target will count the occurrences of the sqlserver.file_write_completed events based on their specific source data element (file_id), allowing for easy identification of the files that are being written to the most frequently.

The session options provided in the above script correspond to the default session options that are defined if an Event Session is created without specifying the WITH() clause at all.  The maximum buffer space allocated for the Session is 4MB, and the session allows single events to be lost if the events are generated faster than they can be dispatched to the targets, the maximum amount of time that an event can be in the buffers before being dispatched to the targets is 30 seconds, the maximum event size is not set, no partitioning of buffers is defined, causality tracking is not turned on, and the session will not start automatically when the SQL Server service starts.

ALTER EVENT SESSION

Creating an Event Session does nothing more than that in Extended Events; it simply catalogs the Event Session definition inside of the Extended Events Engine, if it passes the syntax check, and makes the Event Session available for use.  To actually begin Event collection, the Event Session must be ALTERed to start the Event Collection.  This is accomplished with the ALTER EVENT SESSION DDL command.

	-- ALTER the Event Session to Start it
	ALTER EVENT SESSION [TrackTempdbFileWrites]
	ON SERVER
	STATE=START
	GO
	
	

Once a Event Session is set to STATE=START, it becomes active inside of the Extended Events Engine and begins collecting Events that have been defined in the Session Definition.  An active Event Session can be modified to ADD EVENTs, DROP EVENTs, ADD TARGETs, and DROP TARGETs while the Event Session remains active inside the Extended Events Engine.  When an Event Session only has in memory targets defined on it, dropping all of the defined Events from the Event Session stops the Event Session from capturing further Events to allow for captured Event analysis while preserving the captured Event information for analysis.  If we run a relevant workload against the tempdb database based on the Event Session created above this can be demonstrated.

	USE [tempdb]
	GO
	IF OBJECT_ID('Test') IS NOT NULL
	   DROP TABLE Test
	CREATE TABLE Test (rowid INT IDENTITY PRIMARY KEY, exampledata VARCHAR(4000))
	GO
	INSERT INTO Test(exampledata) VALUES (REPLICATE('abcd', 1000))
	GO 100
	ALTER EVENT SESSION [TrackTempdbFileWrites]
	ON SERVER
	DROP EVENT sqlserver.file_write_completed,
	DROP EVENT sqlserver.file_written
	GO
	SELECT CAST(target_data AS XML) 
	FROM sys.dm_xe_session_targets st
	JOIN sys.dm_xe_sessions s ON st.event_session_address = s.address
	WHERE s.name = 'TrackTempdbFileWrites'
	GO
	

After dropping the events from the session, no further events are captured by the Event Session, which can be shown by running the example workload again and re-querying the targets.

	USE [tempdb]
	GO
	IF OBJECT_ID('Test') IS NOT NULL
	   DROP TABLE Test
	CREATE TABLE Test (rowid INT IDENTITY PRIMARY KEY, exampledata VARCHAR(4000))
	GO
	INSERT INTO Test(exampledata) VALUES (REPLICATE('abcd', 1000))
	GO 100
	SELECT CAST(target_data AS XML) 
	FROM sys.dm_xe_session_targets st
	JOIN sys.dm_xe_sessions s ON st.event_session_address = s.address
	WHERE s.name = 'TrackTempdbFileWrites'
	GO
	
	

While dropping events from a session that utilizes in memory targets allows for captured data evaluation, ALTERing the Session to change the event definition can be useful to react to the event information that has been captured, to provide more specific predicates, minimizing the number of events captured. To change the definition of a specific event already defined on an Event Session requires that the Event Session be ALTERed with a DROP EVENT definition for the specific Event and then that the Event Session be ALTERed with an ADD EVENT clause to add the Event back to the Event Session with the new Predicate Definition. For the purposes of the demo presented in this blog post, since we have already dropped the sqlserver.file_write_completed event, all we have to do is issue an ALTER EVENT SESSION command with an ADD EVENT specification that adds the new Event specification back to the Event Session.

	-- Add the sqlserver.file_write_completed back with new predicates
	ALTER EVENT SESSION [TrackTempdbFileWrites]
	ON SERVER
	ADD EVENT sqlserver.file_write_completed(
	   SET collect_path = 1
	   ACTION (sqlserver.sql_text)
	   WHERE database_id = 2 AND FILE_ID = 1)
	
	

Rerunning the workload against the new event session will show that the Event Session now only captures the sqlserver.file_write_completed Events based on the new criteria.

	USE [tempdb]
	GO
	IF OBJECT_ID('Test') IS NOT NULL
	   DROP TABLE Test
	CREATE TABLE Test (rowid INT IDENTITY PRIMARY KEY, exampledata VARCHAR(4000))
	GO
	INSERT INTO Test(exampledata) VALUES (REPLICATE('abcd', 1000))
	GO 100
	SELECT CAST(target_data AS XML) 
	FROM sys.dm_xe_session_targets st
	JOIN sys.dm_xe_sessions s ON st.event_session_address = s.address
	WHERE s.name = 'TrackTempdbFileWrites'
	GO
	

If the Event Session definition proves to be to much for the ring_buffer target to maintain the data in a useable fashion, it may be preferable to instead capture the events to the asynchronous_file_target, which isn't constrained by the memory configuration of the ring_buffer target. At add the asynchronous_file_target, an ALTER EVENT SESSION statement can be used with the ADD TARGET command to add the new target to the Event Session, and a subsequent ALTER EVENT SESSION statement can be used with the DROP TARGET command to remove the ring_buffer target once its captured data has been consumed from the ring_buffer target.

	-- Add a file target
	ALTER EVENT SESSION [TrackTempdbFileWrites]
	ON SERVER
	ADD TARGET package0.asynchronous_file_target
	( SET filename = 'C:\SQLBlog\TrackTempdbFileWrites.xel',
	     metadatafile = 'C:\SQLBlog\TrackTempdbFileWrites.mta')
	GO
	-- Drop a ring_buffer target   
	ALTER EVENT SESSION [TrackTempdbFileWrites]
	ON SERVER
	DROP TARGET package0.ring_buffer
	GO
	
	

The only part of an Event Session that can not be modified without restarting the Event Session is the Session Options. To change a Session Option, for example the EVENT_RETENTION_MODE from ALLOW_SINGLE_EVENT_LOSS to NO_EVENT_LOSS, requires that the Event Session first be stopped and then restarted. An attempt to change a session level option for an active Event Session results in the following Error:

Msg 25707, Level 16, State 1, Line 2 Event session option "event_retention_mode" cannot be changed while the session is running. Stop the event session before changing this session option.

To change an Event Session level option, requires that the Event Session first be stopped, the option be changed with ALTER EVENT SESSION, and then the Event Session be started again.

	-- Stop the Event Seession first
	ALTER EVENT SESSION [TrackTempdbFileWrites]
	ON SERVER
	STATE=STOP
	GO
	-- Change Event Retention Mode
	ALTER EVENT SESSION [TrackTempdbFileWrites]
	ON SERVER
	WITH (EVENT_RETENTION_MODE = NO_EVENT_LOSS)
	GO
	-- Start the Event Seession after the change
	ALTER EVENT SESSION [TrackTempdbFileWrites]
	ON SERVER
	STATE=START
	GO
	

DROP EVENT SESSION

To DROP a Event Session from the Extended Events Engine to prevents its future use, the DROP EVENT SESSION DDL command can be used. The DROP command can be used against an Active Event Session as well as against a Sessions that are simply cataloged in the Engine and not active.

	DROP EVENT SESSION [TrackTempdbFileWrites] ON SERVER
	

What’s next?

Now that we have how to manage Event Session in Extended Events, in the next post we’ll look at how to find information about the Event Sessions that have been defined in a SQL Server Instance.