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…
Wouldn't it be great to just quickly and easily take a look at your system to see if you had any queries that ran for a long time, but, without actually doing any work to capture query metrics? Oh, yeah, I can do that right now, and so can you. All we need is something that is built into every single server you currently have under management (sorry, not Azure SQL Database) that is SQL Server 2008 or better: system_health system_health Extended Event Session The system_health Extended Events session is very well documented by Microsoft here. I've talked about it before, in relation to Azure SQL Database, and, on the same topic we're going over today, long running queries and waits (cause if you have a long running query, you…
Extended Events can do things that simply are not possible with Profiler and another example comes from the stack of audit events that exist only in Extended Events. One of these is a set of expanded events for database auditing. Comparing the list of things exposed through Extended Events to those exposed through Trace/Profiler isn't entirely fair. All new functionality is only in Extended Events since Trace hasn't been updated since 2008. However, these events that you can use to audit your database, aren't new functionality, they're just new events for watching old functionality. The addition of new events is just one more reason why moving to use Extended Events is a must. Auditing Databases In this instance, when I say audit the database, what I mean is keep an…