Create SQL Server Extended Events Session Using SSMS Query Editor


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

ADD EVENT sqlos.wait_info(
    WHERE ([sqlserver].[database_name]<>N'master')),
ADD EVENT sqlserver.auto_stats(
ADD EVENT sqlserver.database_dropped,
ADD EVENT sqlserver.lock_acquired(SET collect_database_name=(0)
ADD TARGET package0.event_counter,
ADD TARGET package0.ring_buffer

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 (*).

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.


get free sql tips
agree to terms

Learn more about SQL Server tools