Sure, you can right click on a running session for Extended Events and open the Live Data window, but are there other ways of observing what Extended Events is capturing? Yes. Extended Events Output Let me just get out of the way immediately, there's probably a way to do this programmatically with DBATools. I need to dig in there, and I'll post what I find. But what can we do right now? SQL Server Management Studio (SSMS). Here are all the sessions currently on my test/demo machine: Some of these are built in, system_health for example. Most of the rest are mine. So, how can we see what they do? Well, notice that every one has a little plus sign next to it: If we click on the plus sign,…
Once more, my apologies for being late on getting the T-SQL Tuesday announcement out. I have no excuse. However, our extended event on Extended Events (yes, I'm the third person to make this joke, yes, I'm blatantly stealing) still has several entries, so let's talk about them. Let's get mine out of the way. I was simply curious what the search engines revealed when I asked a pretty common question: how do you identify slow queries? What I found was, the answers on most search engines to this question are old, very old. Not to say wrong, but since many of them were created before a working version of Extended Events (let alone Query Store) was released, how could they tell you. On to actually good posts. One of my…
For the longest time, we didn't have one of the most useful tools for monitoring SQL Server behavior, but I just found out that, indeed, you can use Extended Events in AWS RDS. I'm not waiting around. Let's see it in action. Setup For Extended Events in AWS RDS AWS has posted the documentation on what you have to do in order to enable the collection of Extended Events within RDS. Normallly, I'd follow along with the documentation. However, I'm going to approach this like I knew that Extended Events support was there, but I wasn't aware of the docs. So, I'm starting in SSMS and I'm just going to try plugging in the Extended Events GUI to see what happens. Further, I'm going to use the simplest method for…
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 was contacted by Stellar Info quite a while ago. They asked me to try out their software. I said yes, but I was really bad about getting it done. Well, I finally got off my bottom and did the job. So, let's talk about Stellar Repair for SQL Server. Stellar Repair Let's start with the most important piece of information you need: it works. The software itself is really simple to use and just does what you need, repairs your corrupted SQL Server instance. On that alone, I can recommend the tool. However, there are a few gotchas I ran into along the way. Mostly, little stuff. It's things a little polish in the UI and some clean up around language could help out. Don't get me wrong, I'm…
The moment you decide you want to monitor your SQL Server instance for query behavior, you're going to be forced to make a bunch of choices. Further, all these choices lead to problems that you're going to have to deal with. Some of the problems are obvious. For example, you decided to capture all the statements run against the system. That's a lot of data you'll have to be prepared to manage. However, other problems are extremely subtle and can really be a pain. For example, how do you differentiate between a query run by a person in SQL Server Management Studio and the queries that SSMS itself runs against the system? Queries From SSMS Let's say I'm interested in capturing both the batch completed event and the statement completed…
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…
The single most important thing to remember about Extended Events is that this functionality is not simply a replacement for Profiler/Trace, but a whole new tool with new functionality. My first example for functionality that you simply cannot get in Profiler/Trace is the ability to output to a Histogram. Profiler/Trace can output to a table or to a file. Extended Events can have a target that is a file, same as Profiler. However, you can also have a target: etw_classic_sync_targetevent_counterhistogrampair_matchingring_buffer Read about each of the types in the Microsoft documentation here. I'm going to focus for the moment on the histogram target because it lets you do some fun stuff and easily collect data that you simply can't collect using Profiler/Trace without hopping through a bunch of flaming hoops. Setting…
One complaint I've received frequently is that you can't see stored procedure parameter values in Extended Events. That is patently not true. However, it does depend on where and how you capture the events and which stored procedure parameter values you're going for. I think this is a holdover from 2008 when Extended Events... well, let's be kind and say... didn't work well. Now, they do. Let's explore this a little. Capturing Stored Procedure Executions As with most things, there's more than one way to capture stored procedure execution in Extended Events. First up, it depends entirely on how they're called and on your intentions when you capture the information. Here are the three methods I know to capture just the completion metrics on stored procedure calls: rpc_completed sql_batch_completed module_end…