Knowledge of how your system behaves is vital to better control, maintain, and grow the system. While Azure provides all sorts of wonderful assistance within Azure SQL Database, you’re still going to need that same knowledge. When it comes to getting detailed information about Azure SQL Database, the tools are a little more limited than with an on-premises instance of SQL Server, or any virtual instance of SQL Server. There are no trace events. To see individual query calls, recompile events, query store behaviors, and so much more, you’re going to have to use Extended Events.
I’m going to write a series of posts on using Extended Events with Azure SQL Database. Tradition would call for this first post to be an initial how-to. Instead, I want to take a slightly different tack and talk about some of the limitations we’re going to have to deal with.
I have long advocated for the use of Extended Events. I’ve been posting all sorts of blog posts on how to implement them, how they present unique opportunities for new and interesting data, and how they do so much more than the old trace events, yet, put less of a load on the system. All of that is true, until we hit Azure SQL Database.
Now, don’t get me wrong, Extended Events are still awesome, amazing and wonderful. It’s just that, Azure SQL Database is going to force us to hop through a few hoops. I want to be up front with these… I’m trying to find the right word here, challenges? Maybe. Frustrations? Yeah, kind of. Limitations? Again, sort of, but not quite. We’ll settle on as neutral a term as possible: differences. For the moment.
Differences for Extended Events and Azure SQL Database
Microsoft has a great explanation of Extended Events and Azure SQL Database, along with some really good example scripts. This can certainly get you going on the topic. However, it doesn’t point out a few details that really matter. Let’s talk about the biggest one. The Live Data Explorer.
I would argue that this tool, the Live Data Explorer, is the one thing that should make an easy adoption of Extended Events possible. Because it gets the biggest complaint about Extended Events out of the way, no XML. I’ve got a bunch of blog posts showing this functionality off. I use it all the time. Until I hit Azure SQL Database.
When I’m experimenting with Extended Events, one of the first things I’ll do is turn on the telly. By that I mean, I’ll create a Session with events writing out to the ring buffer and then turn on the Live Data Explorer window to see what’s happening.
You can’t do that in Azure SQL Database. Oh, you can output to the ring buffer. You can even see the data stored there easily. Using Management Studio, expand your database in the Object Explorer window. Then find and expand “Extended Events” and “Sessions”. Under that, you’ll see the Sessions you’ve created for this database. Expand one of those and you’ll see the Targets. If you have the ring buffer as a target, you can double click it to view it. You get this:
Does that… is it… well, click on it and see:
Yep. We’re back into XML.
Now, we’ll get to how to once more put the Live Data Explorer window to work in a later blog post. Just know that, those hoops I mentioned earlier? Yeah, well, we’ll be doing some hopping to make that work.
There are a bunch of other differences too. One, slightly painful one, in the targets, no Histogram target. However, there is an alternative, unique to Azure SQL Database, called Event Counter. It’s basically the same as a Histogram, but it does mean some differences in how you code stuff. Again, hoops, hopping.
Another is how you query the behavior of your Sessions. There are different system views and dynamic management views to query. Some more hoops, although, far less painful than some of the others.
So, in short, a tiny bit of the magic is just different.
Conclusion
I decided to start this series of posts with one that is a bit of a downer for a couple of reasons. First, I want to get some of the bad news out of the way early. Second, I know I come off like a cheerleader on this topic, so I wanted to show you that, yeah, I do understand that there are challenges, oh, I mean, differences, here. However, as you’re going to see over time, while there are differences, the functionality of Extended Events is still pretty darned wonderful AND easily accessible, even if we’re talking about Extended Events and Azure SQL Database.