By: Ben Snaidero
Overview
As with everything in SSMS that has a GUI interface the same configuration can be done using standard TSQL. 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 TSQL script directly from within Object Explorer. 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.
