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…
While XML is, without a doubt, a giant pain in the bottom, sometimes, the best way to deal with Extended Events is to simply embrace the XML. Now, I know, just last week, I suggested ways to avoid the XML. I will freely admit, that is my default position. If I can avoid the XML, I will certainly do it. However, there are times where just embracing the XML works out nicely. Let's talk about it a little. Copy This Query I have a theory. It goes like this: There has only, ever, been a single XML query written from scratch. All other XML queries are just copied from that one and then edited to do what is necessary OK. Maybe that's not entirely true. In fact, I know it's…
One story I hear over and over goes like this: I tried setting up Extended Events, but then I saw the output was XML so I stopped. Look, I get it. I don't like XML either. It's a pain to work with. It's actively difficult to write queries against it. If there weren't a ton of ways to avoid the XML, yeah, I would never advocate for Extended Events. However, here we are, I have ten pages of blog posts that at least mention Extended Events. Why? Because I avoid the XML (most of the time). Lots of other people do as well. You can too. Let's see how. Live Data Window I have a video that goes into this in detail right here. But the core concept is simple.…
I just received a question about Extended Events: What about filtering on the stored procedure name. You know I love writing and talking about Extended Events. The answer is, well, sure, we can do that. However, as with all things, there may be wrinkles worth being aware of. Let's examine this. Filter on Stored Procedure Name Let's create an Extended Event session that captures rpc_starting and rpc_completed: CREATE EVENT SESSION StoredProcedureName ON SERVER ADD EVENT sqlserver.rpc_completed (ACTION ( sqlserver.database_name ) WHERE (object_name = N'AddressByCity') ), ADD EVENT sqlserver.rpc_starting (SET collect_statement = (1) ACTION ( sqlserver.database_name ) WHERE (object_name = N'AddressByCity') ); I've added a WHERE clause to the Extended Event to capture only those procedures that have an object_name equal to 'AddressByCity'. If we look at the output from these…
I asked this question myself: Is there a way to use Extended Events to capture the T-SQL of a prepared statement? Why would I be concerned with prepared statements? Wouldn't sql_batch_completed and rpc_completed cover us? Well, no. What happens when you use sp_prepare? What happens when you're using an ORM tool that's using prepared statements? You may see queries that look like this: EXEC sp_execute 5, 48766; What the heck code is that executing? Let's find out. sp_statement_completed Here's a set of sample code that I swiped from Microsoft (they don't mind, but, full attribution like a good citizen, you'll find it here): DECLARE @P1 int; EXEC sp_prepare @P1 output, N'@Param int', N'SELECT * FROM Sales.SalesOrderDetail AS sod INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID WHERE SalesOrderID =…
I was recently asked a question on a forum by a person who was frustrated with all the tool choices we have for measuring performance. Moreover, they were frustrated that a simple and clear combination of the tools to achieve synergy was extremely challenging. In fact, they said that, just using the query_hash as an example, they never saw a single match between the DMVs, Query Store and Extended Events. Now, that's pretty unlikely and I'm sure we could talk about why that might be the case. However, this idea of combining the tools, I shared a bunch of thoughts on it. I decided, maybe it's worth sharing here too. Achieving Synergy Honestly, this is tough. I work for a company that makes a monitoring tool. We are trying to…
Over the next couple of months, I'll be putting on a number of different sessions teaching about the tools supplied by Microsoft, for free, that can help you when tuning your queries. One of the most important of these tools is Extended Events. A couple of my sessions in the Redgate Community Circle livestream "Built-in Tools Make Query Performance Tuning Easier" will be on Extended Events. My livestreaming starts tomorrow, April 21, at 2pm Eastern. It will be recorded and made available for free. Follow the link for all the details, or, just subscribe to Redgate's YouTube account. I'm also going to be hosting a fundamentals introduction to Extended Events, "The Easy Way to Extended Events." Heck, I'm even going to be hosting a session showing how to use Extended…
Wouldn't it be great to be able to put together queries and waits at the same time? You all capture query metrics using some method. Most of us query sys.dm_os_wait_stats or sys.dm_db_wait_stats. Combining them is hard. You could query the wait stats. Store the results in a table variable. Run the query in question. Then query the wait stats again into a different table variable. Join the two table variables together to find the differences. Ta-da, you have query waits. Well. Probably. If you're the only one running queries on the system. Also, you're not seeing system waits or other noise caused by activity on the system. Or, we could put Extended Events to work. Queries and Waits Just like Profiler/Trace, you can capture stored procedures, batches, and individual statements…
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…