So far I have strictly focused on Extended Events for SQL Server installations. This means I have been looking at Extended Events from the “physical” or on-premises perspective. Installing SQL Server into an Azure VM falls into this category as well.
What hasn’t received any TLC is Extended Events from the Azure SQL Database perspective. You may be wondering why it matters since I just said that Extended Events is the same in an SQL Server in an Azure VM as it is on-premises. That is Azure and that is the cloud right?
Well, not entirely. Azure SQL Database works quite a bit differently. Azure SQL Database introduces some things into Extended Events that basically turn your XEvent world upside down. Sort of. If you understand that Azure SQL Database is a contained database (in concept), then some of this world flipping I mentioned is merely a tilt and not terribly earth shattering. That said, it does require at least a little bit of attention. Today, I will merely introduce some of the differences and then leave the internals for a later time.
Azure SQL Database
Azure SQL Database uses some of the same dynamic management views(DMVs) and catalog views as SQL Server. I covered those DMVs and catalog views in previous articles here and here – respectively. For the most part, Azure SQL Database uses a different set of views though. Some of the views of interest for querying metadata are listed in the following tables:
|sys.dm_xe_database_session_event_actions||Info about actions within active event sessions|
|sys.dm_xe_database_session_events||Info about events within active event sessions|
|sys.dm_xe_database_session_object_columns||Info about event payload for events in an event session|
|sys.dm_xe_database_session_targets||Info about targets within active event sessions|
|sys.dm_xe_database_sessions||Returns a row for each active database scoped event session.|
Looking at the names in the list they should be pretty familiar at this point. The real difference is that these are scoped to the database whereas SQL Server is not scoped in the same manner. This is an essential distinction due to the way Azure SQL Database works.
|sys.database_event_session_actions||Info about actions within deployed event sessions|
|sys.database_event_session_events||Info about events within deployed event sessions|
|sys.database_event_session_fields||Returns a row for each customize-able column that was explicitly set on events and targets.|
|sys.database_event_session_targets||Info about targets within deployed event sessions|
|sys.database_event_sessions||Returns a row for each event session in the SQL Database database.|
These particular catalog views should also seem somewhat familiar. The major difference in name being that they are scoped to the database instead of server just like the DMVs.
XE Internals Metadata
|sys.dm_xe_map_values||Returns mappings of internal keys to text|
|sys.dm_xe_object_columns||Metadata for object data definitions|
|sys.dm_xe_objects||Metadata for different available objects within XE Engine|
|sys.dm_xe_packages||Listing of packages registered within XE Engine|
And lastly, these are all the same between SQL Server and Azure SQL Database. If you have been following along with the series, these views really should come as no big surprise and there should be a base familiarity with them. That familiarity is part of the reasoning for skipping a deep dive at the moment.
Azure SQL Database still give the possibility of storing event data in multiple different types of targets. Not all standard public targets are available for user defined sessions. One can currently use the ring_buffer, event_counter, and event_file targets. This provides for two different memory type of targets and one file target. The file target does not come without a cost though. To use the file target does require access to an Azure Storage Container and will take some extra effort to properly configure.
Azure SQL Database also comes with a set of default sessions automatically configured. Not all are running just like in SQL Server, but they are deployed. Even though several sessions are deployed by default, there is a limitation to how many can be running at a time (subject to change). The default sessions are (subject to change):
Despite these differences, there is a fair amount of consistency. Discovery of objects and metadata still remains the same. Being able to query the metadata for running or deployed sessions is also in line with the previous articles I have written in this series. In short, despite the slim differences (subject to change) and the platform differences between SQL Server and Azure SQL Database, there are still consistencies and common building block practices that I have shown still apply.
Even though there is a great amount of consistency throughout the extended events engine and between these two disparate platforms (SQL Server and Azure SQL Database), there is one glaring difference that must be discussed. I showed that the catalog views and DMVs are scoped to the database instead of server. Guess what that means for creating the actual session? You got it! An Extended Event Session is scoped/created at the database level rather than the server level. This is done easily as follows:
EVENT SESSION myAzureDBScopedXESession
ADD EVENT ...
As I have said, I am not going to go deep into the details and inner working of Extended Events in Azure SQL Database at the moment. I will save those deeper dives for a later time. What I have done at this point is to introduce the basic differences and introduce the realm of XE in Azure SQL Database to you. Don’t be afraid to use Azure SQL Database based off a previous limitation around Extended Events. Things are changing in the cloud and they are changing at a rapid rate.
I hope you enjoyed the article.
This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.