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.

Mapping Events from Trace to Extended Events

Every time I talk about migrating from Profiler and Trace to Extended Events (XE), I seem to add something to my presentation.  It’s not always intentional, and I don’t know if it’s because I just can’t stop tweaking my demos and contents, or something else, but here in London at IEPTO2 this week, I added a query that helps you see what event in Extended Events is comparable to the event you’re used to using in Trace/Profiler.  While most of the events in XE have a name similar to the event in Trace (e.g. sp_statement_completed for SP:StmtCompleted), some mappings aren’t so intuitive.  For example, SP:Starting in Trace is module_start in XE, and SP:Completed in Trace is module_end in XE.  That’s not so bad, but if you’re ever monitoring file growths, note that the database_file_size_change event in XE is the event for the following four events in trace: Data File Auto Grow, Data File Auto Shrink, Log File Auto Grow, and Log File Auto Shrink.

(Note: if you happen to notice databases_data_file_size_changed or databases_log_file_size_changed in the list of XE, do not use either event.  Both are deprecated and won’t return any information.  Use database_file_size_change.)

Anyway, those fun one-offs aside, you’ll see that the query and its output are pretty straightforward:

SELECT 
	[t].[trace_event_id] [TraceEventID], 
	[t].[name] [Trace_Event], 
	CASE	
		WHEN [t].[name] LIKE '%Audit%' AND [xe].[xe_event_name] IS NULL THEN 'This event is not implemented in Extended Events, it may be a Server Audit Event'
		WHEN ([t].[name] LIKE '%Audit Lo%' OR [t].[name] LIKE'%Audit Server%') AND [xe].[xe_event_name] IS NOT NULL THEN 'The XE Event [' + [xe].[xe_event_name] + '] exists, but use Server Audit for this event instead'
		WHEN [xe].[xe_event_name] IS NULL THEN 'No comparable XE Event'
		ELSE [xe].[xe_event_name]
	END	[XE_Event],
	[xe].[package_name] [XE_Package]
FROM [sys].[trace_events] [t] 
LEFT OUTER JOIN [sys].[trace_xe_event_map] [xe] ON [t].[trace_event_id] = [xe].[trace_event_id]
ORDER BY [t].[name] ;
GO 

You can run this on SQL Server 2012 or higher.  You could run it on SQL Server 2008/2008R2, but remember that there was not a comparable event in Extended Events for every event in Trace until SQL Server 2012.  Thus you will have some unmapped events in the 2008/2008R2 releases.

Something that you’ll notice in the query: I’m accounting for Audit events.  The  Audit Feature was introduced in SQL Server 2008 but was Enterprise Edition only.  In SQL Server 2012, Server Audit is supported in all editions, so all Audit events from Trace should instead be captured with Server Audit (which leverages Extended Events behind the scenes).

Finally, don’t forget that if you’re looking to take an existing Trace and convert it to Extended Events, you can use Jonathan’s stored procedure to get the event session DDL.

Happy Extended Eventing!

 

SQL Trace versus Extended Events: How long does it take to startup a trace vs. an event session?

Yesterday I asked how everyone uses Trace and Profiler…  Jonathan and I have talked about creating a library of Extended Events script to help people migrate off of Profiler and Trace and that post was designed to generate some ideas about what people might find useful.

I haven’t gotten a ton of comments, but I did get a few (thank you to those have responded!), and I decided to take one of them and create a Trace and create an Extended Events session and see how long it took for each.  Jonathan has mentioned before that he can create an XE session as fast as a Trace, and I’ve been thinking that I can as well so I thought I’d test it.  It’s a straight-forward Trace versus Extended Events test.  Want to see what’s faster?  Watch the video here.