mssqltips logo

Create SQL Server Extended Events Session Using SSMS New Session Wizard



By:
Overview

Using the New Session Wizard exposes most (but not all) of the Extended Events functionality and is probably the easiest/quickest method to create a new Extended Events session.  To start the wizard you need to open Object Explorer in SSMS and expand the "Management" folder, then the "Extended Events" node and then right-click on "Sessions" as shown below and select "New Session Wizard".

ssms object explorer

Once you click on the "New Session Wizard" menu item you’ll be presented with the window below where you can click "Next".

extended events new session wizard

After clicking "Next" the following window will open where you can give your session a name and check if you want the session to start at server startup.   Once done click "Next".

extended events new session wizard

The next window allows you to select a pre-configured event session template.  We will go through this option later in the tutorial.  For now, leave "Do not use a template" selected and click "Next".

extended events new session wizard

In the next window we can select the events we want to capture in our session.  Notice that use can use the box highlighted by the green arrow to filter the list of events.  For this example we are just interested in deadlocks so we will filter on this and then select the deadlock report.  You use the arrows between the two boxes to select and deselect events.  Also notice that when you highlight an event in the event list on the left you get a list of the event fields along with a description as shown below (red arrow). 

extended events new session wizard

Once you are done selecting all the events you require in your session you can click "Next" and you will be presented with the following window where you can select any global fields.  These are fields which are common to all events and for this example we will just add the "current call stack" and select "Next".

extended events new session wizard

The next window in the wizard allows us to add any predicates (or filters) to our Extended Events session.  Clicking on the "Field" column will give you a list of predicates that you can filter on.  For this example we are only concerned with the database named Production so we will add a filter on database_name.

extended events new session wizard

Once we are done selecting all the filters we want to add we can select "Next" and the following window will open where we can define the target for our event data.  The wizard gives us the option to use the event_file or ring_buffer targets.  Notice that for both options we have the ability to limit the file/buffer size and for the ring_buffer target we can also limit the number of events.  Once you are done selecting your options as shown below you can select "Next".

extended events new session wizard

Now you are presented with a summary window which shows you all the options that you selected in the previous steps.  Notice in the below screenshot that there is a script button, pointed to by the red arrow, which when clicked will open a TSQL script in a new SSMS query window that you can also use to create this session on this or any other server.  For this example we will just click the "Finish" button to create the session.

extended events new session wizard

After clicking "Finish" the following window appears confirming that your session was created.  You are also given another opportunity here to start the session and also to monitor the session in real time.

extended events new session wizard

Next we will cover other methods to create an Extended Events session.

Additional Information

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