In my last post I showed some shortcomings of Extended Events, however, it is possible to use Live Data with Azure. Let's explore exactly how that works. To get started, you'll need to follow the directions here to get set up with Azure Storage as the output target of your Extended Events session within your Azure SQL Database. There is a little bit of prep work, but it's all laid out in Microsoft's document. I found the Powershell to be a bit sketchy, but it shows you what's needed. The T-SQL just works. Live Data With Azure Once you've created an Extended Events Session that is output to Azure Storage, you've done most of the work. The trick is really simple. Get the Azure Storage account set up with a…
Of all the things that Extended Events does, I've found the ability to quickly and easily gather a little bit of data and then use the Data Explorer window Live Data grouping to aggregate it to be one of the greatest. Sure, if we're talking about using Extended Events on a busy production server, this method probably isn't going to work well. There, you are going to be better off querying the XML (I know, I know, but I have ways to help there too). But in development, when doing testing and query tuning, the Live Data window is a gift of the gods on par with fire or beer (it's not as good as whiskey). Live Data Grouping Let's imagine a scenario like this. You're working on some query…
One reason a lot of people don't like Extended Events is because the output is in XML. Let's face it, XML is a pain in the bottom. However, there are a bunch of ways around dealing with the XML data. The first, and easiest, is to ignore it completely and use the Live Data window built into SQL Server Management Studio. I've written about the Live Data window before, and I've been using it throughout this series of posts on Extended Events. There's a lot more to this tool than is immediately apparent. Today, we're going to explore the basics around this tool Live Data There are two easy ways to get the Live Data window open. The first, for any Extended Event session that's running, you can right click…
There are so many reasons to love Extended Events that it can really be overwhelming, but, one more reason that I love them are the ability to customize Live Data. More importantly, you can share that customization with others or move it between machines. Let's explore this. Customize Live Data If you open up an Extended Events file, or connect to a running Session, you're looking at the Live Data window and it looks something like this: I won't lie, that's a pretty ugly UI. At the top are all the events you've captured. At the bottom are the event fields. If you want to look at the information in different events, you click on the event, then down at the fields... blech! However, I have some options. Let's right…
The all new, in-person, PASS Data Community Summit is in just a few weeks. Since I'm shortly going to be publishing a 100% rewritten book on query tuning, I decided to take a look at what kind of sessions on the topic are going to be available at the Summit. Query Tuning With The Best You can search through the published sessions here. I didn't even bother typing in "query tuning". Far too lazy. I just typed "query" and already saw some amazing stuff. In no particular order... One of the people I've ALWAYS learned from, Kimberly Trip is delivering a precon, Indexing for Performance. That's going to be a great day of good learning. Kim is insanely knowledgeable and a fantastic presenter. Well worth your time. Erin Stellato, another…
I have long been a fan of Azure Data Studio, but one shortcoming has kept me from truly adopting it: Query Plans in Azure Data Studio. Sure, there was a plug-in you could install. Also, you could use a somewhat truncated version of Plan Explorer, but all I wanted was for SQL Server Management Studio plans to be query plans in Azure Data Studio. Go and get version 1.35 of the tool. Right now. DUDE! You have 1.35 of Azure Data Studio? Cool. Now, go to the menu bar. Click on "File." Click on "Preferences". Click on "Settings". Now, type the following into the search box: workbench editor enable preview. You should see this: Check the box below where it says "Workbench > Editor: Enable Preview" just like I have…
I recently saw a question about the Azure Data Studio Intellisense: "Why won't intellisense in Azure Data Studio work with different schemas?" Immediately I thought, "Wait, it does." But, testing is your buddy. Azure Data Studio Intellisense Azure Data Studio Intellisense is on by default. Also, I like it a little better than the one in SSMS because it will start trying to help you, as you type (like a 3rd party software I can't live without). However, they're partly right. Let's say I want 'Person.Address' so I start typing like this: It doesn't know that I have a table named 'Person.Address'. It's trying to be helpful. You can even see how it's doing a form of wild card search, suggesting that maybe 'db_accessadmin' is what I want since it…
I sincerely hope this isn't the first time you're hearing about the Pass Data Community Summit that's coming up in just a few weeks on November 8-12, 2021. But, just in case you haven't heard, let me tell you about a few things I'm personally excited about. Networking! There is a ton of stuff getting worked on to make this an interactive event. Even though this will be an online event (and a free one), we're doing everything we can think of to get you a community experience. We're providing mechanisms for all sorts of different networking. You'll be able to give a few virtual hugs to those you've missed. Learning! Check out the list of sessions. Everything from straight up, good, old fashioned query tuning in SQL Server, to…
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…
Redgate published a report that many of you helped with by providing information. First, thanks! Your info really helped. Second, do you want to see it? You can. Go here and get a copy. However, want to talk about it? Anthony Nocentino and I are hosting a little chat this week on Wednesday at 4-5 BST, 10-11 Central. You can click on this to get registered. Please do. It's going to be great information and, since it'll be live, you can ask questions. It will be recorded and you can watch it later. However, where's the fun in that? Join in. Be there live.