Create SQL Server Extended Events Session Using SSMS From Template



By:
Overview

You might have noticed in both the "New Session Wizard" and the "New Session" dialog that when we were at the initial step of creating our session there were event session templates available.  SQL Server comes with quite a few predefined templates that can be used to setup a session for some common sets of events.  You can also create your own templates and save them on a server for future use.  Here is a complete listing of the pre-defined templates that are available.

  • Locks and Blocks
    • Count Query Locks
  • Profiler Equivalents
    • SP_Counts
    • Standard
    • TSQL
    • TSQL SPs
    • TSQL_Duration
    • TSQL_Locks
    • TSQL_Replay
    • Tuning
  • Query Execution
    • Query Batch Sampling
    • Query Batch Tracking
    • Query Detail Sampling
    • Query Detail Tracking
    • Query Wait Statistic
  • System Monitoring
    • Activity Tracking
    • Connection Tracking
    • Database Log File IO Tracking

For this example, we will walk-through creating an extended events session using the "New Session" dialog and the "Query Wait Statistic" template.  As we did in the previous example, we can select the "New Session" dialog from Object Explorer and a dialog will open to the "General" page.  Here we can fill in the session name as we did before and notice below this is the template dropdown (circled in red).  Here we can select the "Query Wait Statistic" template.

extended events templates

Once selected if we move to the "Events" page we can see that there are a bunch of events already selected and configured.

extended events templates

Similarly, on the "Data Storage" page we have an event_file target configured.

extended events templates

You can make changes to any of these items as you require or you can just click "OK" and create the session as it’s defined.

We will continue with a few more ways to create an Extended Events session.


Last Update: 6/4/2019






Comments For This Article





download








get free sql tips
agree to terms


Learn more about SQL Server tools