system_health Extended Events in Azure SQL Database

The system_health Extended Events session is incredibly useful. Further, it’s running, by default, in every server you have under management that is 2008 or greater. Things are not the same in Azure though.

system_health in Azure SQL Database

If you look at the documentation for system_health, it shows that it’s applicable to Azure SQL Database. However, if you try to run the example query, it won’t work. This is because the implementation of Extended Events inside Azure SQL Database is a little different. Instead, you need to use the Azure SQL Database equivalent system views to create the same query like this:

Now, running this in Azure, prepare to be disappointed. While the system_health documentation says it applies to Azure SQL Database, there is not a system_health session there.

Can we make one?

Recreating system_health in Azure SQL Database

Referring back to the documentation, the u_tables.sql file in the Install directory of your on-premises SQL Server instance has the script needed for recreating the system_health session.

Of course, we can’t simply run this as is on Azure SQL Database because one of the two targets is to your LOG folder. No such thing in Azure. First thing I did was remove that target. Then, you can’t use the ‘SERVER’ key word in the commands, so I replaced that with ‘DATABASE’. Running the script resulted in an error:

Msg 25744, Level 16, State 1, Line 20
The action ‘package0.callstack’ is not available for Azure SQL Database.

OK. Time for some quick research. So, the call stack is of a lot more interest to Microsoft when debugging than it is to me, so I’m going to strip this action and see what we get next. After a bunch of trial and error, I ended up with this script:

That gets me a bunch of the interesting stuff from system_health, but, it is missing one thing that I left in the script, just commented out; xml_deadlock_report. That sucks just a little. Although, there is already a way to capture deadlocks in Azure SQL Database, so it’s not necessarily needed in your new, fake, system_health session.

Conclusion

The short answer to, is there system_health in Azure SQL Database question, is no. At this time, that does not exist. However, can we at least simulate it with a little work? Yeah, but, I did not spend a lot of time investigating what all the events that I eliminated (almost all related to buffers of one sort or another) are causing me to lose. What I did get though are the long running queries, waits, errors, and other stuff on display within the system_health extended events session. It’s not everything, but it’s a start.


If you want to learn more about how to use Extended Events and other tools built into SQL Server that will help you identify and tune queries easier and faster, I have some all-day seminars coming up on the topic:

SQL Day, May 13-15, Wroclaw, Poland
SQLSaturday Columbus Precon, June 7, Columbus OH

3 thoughts on “system_health Extended Events in Azure SQL Database

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.