Azure SQL Database

So far I have strictly focused on Extended Events for SQL Server installations. This means I have db_cloud_greenbeen 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

Metadata

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:

Running Sessions

DMV Description
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.

Deployed Sessions

Catalog View Description
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.

Storage

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.

Default Sessions

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):

  • azure_xe_activity_tracking
  • azure_xe_errors_warnings
  • azure_xe_object_ddl
  • azure_xe_post_query_detail
  • azure_xe_pre_query_detail
  • azure_xe_query
  • azure_xe_query_batch
  • azure_xe_query_detail
  • azure_xe_query_execution
  • azure_xe_query_waits
  • azure_xe_query_waits_detail
  • azure_xe_waits

Consistency

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.

Scoping

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:

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.

 

1 Comment - Leave a comment
  1. […] Azure SQL Database October 28, 2015 […]

Leave a comment

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










Calendar
October 2015
M T W T F S S
« Sep   Dec »
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Monday, March 27, 2017