Overview
As with everything in SSMS that has a GUI interface, the same configuration can be done using standard T-SQL. While you could start from scratch and write a “CREATE EVENT SESSION …” statement, as we saw in the “New Session Wizard,” there was an option at the end to generate a script of your session. I find it easiest to start using this and modify the script as necessary. You can also generate and T-SQL script directly from within Object Explorer.
Explanation
To do this, expand the “Management” folder in SSMS, then the “Extended Events” node, and then the “Sessions” node. You should then see a list of the Extended Event sessions that are configured on your instance, and you can right-click on the session you want to script, as shown below, and select “Script Session as.”
Below is an example of the script generated using the session we created using the “New Session Dialog.”
CREATE EVENT SESSION [First_XEvent_Session_Dialog] ON SERVER
ADD EVENT sqlos.wait_info(
ACTION(package0.callstack,sqlserver.database_name)
WHERE ([sqlserver].[database_name]<>N'master')),
ADD EVENT sqlserver.auto_stats(
ACTION(package0.callstack,sqlserver.database_name)),
ADD EVENT sqlserver.database_dropped,
ADD EVENT sqlserver.lock_acquired(SET collect_database_name=(0)
ACTION(package0.callstack,sqlserver.database_name))
ADD TARGET package0.event_counter,
ADD TARGET package0.ring_buffer
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);
Looking at the script, you can see we have a clause for each step we went through when configuring our session.
- EVENT – name of the session
- ACTION – global fields
- WHERE – predicates/filters
- TARGET – location of target data
- WITH – advanced options
You can now use this script as a starting point and add/remove items as you require to build a new extended session.
Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.
- MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017