XEvent Profiler

XEvent Profiler is not a SQL Server feature, it is a feature of SQL Server Management Studio(SSMS). It was first introduced in SSMS v17.3 and the XEvent Profiler sits on top of Extended Events technology.  Per Microsoft, XEvents “enables quick access to a live streaming view of diagnostics events on the SQL server”.

You can find XEvent Profiler at the bottom of the server tree in SSMS, just under SQL Server Agent. There are two sessions that come with XEvents, Standard and TSQL.

 

Starting and Stopping the Sessions

When you first Launch the session and the Extended Event sessions does not exist, SQL Server will create the session, however it not be started.  To start it you need to right click on the session in XEvent Profiler and click “Launch Session”.  If the Extended Events session already exists, SQL Server will start the session.

When you are done doing what you need to do, simple follow the same steps and click “Stop Session”.

Standard Session

When you enable this session it creates an Extended Events session named QuickSessionStandard.  This sessions does not have a target defined and has a Maximum Dispatch Latency of 5 seconds.  If you create an Extended Events session the default Maximum Dispatch Latency is 30 seconds, significantly higher than the Standard session. This session collects a lot of performance information.  However, it also collects login and logout information. Below you will find the list of events that are being collected.

One thing you might notice about the above image is that there are number Predicates defined.  These are all the same, they exclude system processes.

When you launch the session, SSMS will allow you to watch live events.  The screenshot below is what you will see when viewing live events.  Among the information collected, you will find Database Name, Batch SQL, Client Application, User account and the query_hash.  This view can be modified to meet your needs. The changes to the view will remain when you close the session.

TSQL Session

The second session available is the TSQL session.  The Extended Event session is named QuickSessionTSQL.  As the name implies, this session collects information about the execution of TSQL statements. The events are below.  You notice a few things about these events, like the QuickSessionStandard session, the TSQL session does not have a designated target.

  1. Many are also in the QuickSessionStandard session including:
    • Existing_connection
    • Login
    • Logout
    • sql_batch_starting
  2. There are fewer events than in the QuickSessionStandard session
  3. The QuickSessionStandard session has the sql_batch_completed while this does not

Like the QuickSessionStandard session, the events in this session also have a few predicates applied. All of which are to exclude system sessions from the live data.

Viewing Session Data

When you launch either of these sessions, SSMS will also launch the screen that will allow you to view live data, see below.  Something to keep in mind, when you close this window, it will stop the session.

Modifying the Sessions

Although you can modify the view, it may not be the best idea to modify the definition of these sessions. If you find a need to modify these sessions, it might be better to either modify another user defined Extended Event session or create a new session.

Both of these sessions are intended to provide a quick way to collect and view some basic performance information about your SQL Server.  For those of you that like to use Profiler, this could be a good alternative, even though it is not as powerful or very flexible.  Since neither of these sessions has a defined target, the data is not stored.  Once you disable the session the data is gone.  If you really need this data for review at a later day, you can export it.  The export menu can be found under the Extended Events Menu item.

Thank for stopping by!!!

Additional Information

Microsoft

MSSQLTIPS