Create SQL Server Extended Events Session Using SSMS Query Editor



By:
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".

create extended events session with query

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.


Last Update: 6/4/2019




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools