Extended Events in Azure Data Studio

I was prompted to dig into using Extended Events in Azure Data Studio (ADS) after the latest release came out from the Tools Team this past Monday.  After you download and install ADS, and connect to an instance, you’ll want to navigate to the Extension Manager (blue box in image), then scroll through the list to find SQL Server Profiler (purple box in image):

Azure Data Studio Extension Manager

Azure Data Studio Extension Manager

 

NOTE: Even though the extension is named SQL Server Profiler, it is using Extended Events.  The documentation states explicitly:

“The SQL Server Profiler extension provides a simple SQL Server tracing solution similar to SSMS Profiler except built using XEvents.”

To clarify, the extension in ADS is like XEvent Profiler in Management Studio (which also is built using Extended Events).  The name “SQL Server Profiler” is confusing, as this is not the same tool (UI) that’s been available since SQL Server 7.0.

To install the extension, click on it, and then select Install.  Once it’s installed you can select Reload and it will move into the top half of the window under Enabled.  Notice that when you select the extension, information about how to use it also appears.

SQL Server Profiler Extension Installed
SQL Server Profiler Extension Installed

 

The easiest way to start using Extended Events in ADS is to use Alt+P (for Windows) or Ctrl+Alt+P (for macOS). You can also click on the Manage icon in the bottom left, select Command Palette… and then type Profiler. Using either method, you’ll get a Connection window to connect to the instance:

Connection panel
Connection panel

 

Once you’ve connected, you’ll be prompted to select what type of session to create, and there are three options currently available:

XE New Session
XE New Session

 

This is where things get interesting.

There are three template options:

  • Standard_OnPrem
  • Standard_Azure
  • TSQL_OnPrem

Within XEvent Profiler in SSMS there are only two options:

  • Standard
  • TSQL

The events in the Standard_OnPrem and Standard event sessions are the same.  The events in the TSQL_OnPrem and TSQL event sessions are the same.  The Standard_Azure event session cannot be created for an on-premises installation – it will silently fail.

Once you select the session you want and select Start, a new window opens which is like the Live Data Viewer in SSMS, but with options.  As queries are executing, they will start to appear in the window.  When you have found the information you need, you can stop the event session using the stop button (red box in image, along with Start and Pause) or you can close the window.  If you close the window you will be prompted to stop the event session, which I would recommend doing.  You can also change the view using the Select View drop down (blue box in image), which changes which columns appear in the window.  Lastly, when you have stopped an event session, you can select a different event session which already exists on the instance from the Select Session drop down (purple box in image) and start it from the window.

Session options
Session options

 

A few things worth noting:

  • The Event Sessions created in ADS write to the ring_buffer target, the ones created in SSMS do not write to any target.
  • If you have an event session running:
    • Closing the Profiler window in ADS does prompt you to stop the event session.
    • Closing ADS entirely does not prompt you to stop the event session.
  • You can create the TSQL and Standard event sessions in SSMS, and you can create the Standard_OnPrem and TSQL_OnPrem event sessions in ADS, and theoretically run all of them at the same time.  I wouldn’t recommend this, but if you have multiple users, be aware it’s possible.
  • To see what event sessions are running, you can execute:

SELECT *
FROM sys.dm_xe_sessions;
GO

  • To stop an event session using T-SQL, you can execute:

ALTER EVENT SESSION ADS_Standard_OnPremES
ON SERVER
STATE = STOP;
GO

  • You can still create, alter, and stop event sessions using T-SQL in Azure Data Studio.

8 thoughts on “Extended Events in Azure Data Studio

    1. Hi-

      Can you explain what you mean by “skip heavy events”? If you have configured an Extended Events session, or a Profiler session, to capture an event like xml_showplan, it will capture the event. It doesn’t “skip” them because they are expensive.

      Erin

  1. Erin,

    SQL server Profiler skips (not collecting) XML_showplan if it run as application (not just a trace), and it left note about it. This is for sure. To avoid such you have to run trace by script in the server.

    I’ve made just few tests with XE and it looks like it does not collect big xml_showplans as well, but without any warnings.

    1. Profiler would jettison events (not JUST showplan) if the events could not be moved from the queue (buffers) to the rowset provider (Profiler) in 20 seconds because events were being generated faster than they could be pushed to Profiler. This would manifest with a message in Profiler that events had been lost. That was not specific to showplan events, that could happen for any events.

      Extended Events will also jettison events, either single events or an entire buffer of events, if it cannot dispatch events from the intermediate buffers to the target because it cannot keep up with event generation. Jonathan describes this in detail in his post about XE session options (https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-26-of-31-configuring-session-options/) under the EVENT_RETENTION_MODE section. Again, this is not specific to plan events.

      It’s also possible that the plan is too large to be captured, because of the settings for MAX_EVENT_SIZE. I don’t know if you’re using the default configuration or not, but by default, you have 4MB of memory for the intermediate buffers (determined by MAX_MEMORY), split into 3 buffers (based on MEMORY_PARTITION_NODE), so each buffer is about 1408KB. If the plan is larger than that, it can’t be captured.

      Further, if the MAX_EVENT_SIZE has been changed from the default value of 0, and is smaller than the size of the plan event (which would be a factor of the size of the plan), then the event session can’t capture the plan.

      Hope that helps,

      Erin

  2. After your reply on my twitter question i came across this blog :).

    Now I’ve got a question here as well. I’ve started one extended events session from ADS, but nothing shows up, even though i see things happen in the ring buffer when i’m checking out the session in SSMS. Besides trying to find out why Azure allows for only one trace, now i have to find out what events to log to trigger the live view in ADS. But without this blog, i’d never have thought of ADS. Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.