Comparing Extended Events in SQL Server and Azure SQL Database

Author by Kim Claditis

An exciting new feature of Azure SQL Database is the implementation of Extended Events (XEvents). I use XEvents in SQL Server to track information about many things – query performance, deadlocks, Availability Group status, and more. Until October, they were missing from SQL Database, but they are now in preview!

What’s the same?

The basic premise of XEvents remains unchanged – you create a session with events, where you collect fields, narrow the results with predicates, and save the results to targets.

Most of the T-SQL to create sessions remains the same – the main difference is that creating, altering, or dropping a session in SQL Server uses the command ON SERVER; in SQL Database you use the command ON DATABASE.

The SQL Server version of XEvents is much more full-featured, however.

What’s different?

SQL Server 2014 contains 872 events, compared to 46 in SQL Database. Yes, that’s only 5%! There are events for certain features, such as Availability Groups, Service Broker, and buffer pool extensions that you can’t track in SQL Database.

SQL Database contains 3 targets, compared to 6 in SQL Server. Both allow you to use ring buffer, event file, and event counter. SQL Server also has histogram, pair matching, and ETW. In SQL Database, when you use event file, you are saving to an Azure Storage container, so you need to know how to create and manage those.

There is no GUI to create sessions – you must be familiar with the T-SQL to create and alter sessions. I’ve improved my skills at this by creating sessions in SQL Server using the “New Session …” dialog, and scripting them there.

To read the data, you write XQuery – there is no GUI when connected to your Azure SQL Database through either SSMS or VS. Another option is to use the event file target, download the file from your Azure storage container to your local computer, then open it in SSMS to view the data.

Is it worth it?

Overall, Extended Events is a great addition to Azure SQL Database. It can be frustrating that your existing queries for creating sessions, reading targets, and using DMVs can’t be executed directly. But with a little persistence, and re-writing, you can use XEvents in SQL Database!

Author

Kim Claditis

Marketing Manager