Let me be extremely clear up front, this is not my original work. I saw this post on DBA.StackExchange.com and I wanted to share and promote it. Nice work FevziKartal. The rest of this post is just me replicating work already done by others. I just want to see it in action. Columns in Live Data Explorer Back when I wrote the 2017 version of my query performance tuning book (no link, a) get the 2022 version for reasons I'm about to explain, b) this post is just about testing FevziKartal's work, not self-promotion), I was on board with #TeamXE. Jonathan Kehayias had taken me under his wing and explained the virtues of Extended Events and I was sold. I was also grossly ignorant. I thought that the way you…
Yeah, stupid long title. It's a question I put to several different AI engines. I'm curious what the aggregated knowledge of the internet has to say on the topic of blockers to Extended Events adoption. I'll leave it to you to do the search on your favorite engine and get the wordy, long-winded, answers. However, I want to bring up some of the points that were raised in order to talk about them. First, we'll talk about some of the odd answers. Then, we'll talk about the consensus. Also, one engine gave me a blatantly poor example Session, so I want to point that out. Note: I'm not dinging on AIs. I'm growing to like the little monsters. We're going to address valid points that they bring up. However, I…
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,…
It's a great question. Let's say you want to capture stored procedure completions. But, you only want to capture them between 3AM and 4AM. Can you do it? Output of rpc_completed Let's create a really simple event: CREATE EVENT SESSION [RPCTimeBoxed] ON SERVER ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1) WHERE ([sqlserver].[database_name]=N'AdventureWorks')); If we start this event, run some code, the output within the Data Explorer window looks like this: Just a couple of points here. Notice the fields in the event. None of them are dates or times. However, up above, we get the timestamp column. Done, right? Let's use that. But first, what does AI, through CoPilot tell me? CoPilot To The Rescue? I asked CoPilot. It took a couple of refinements to get it on board with the idea that…
With 165 T-SQL Tuesday events, two, just two, this one, T-SQL Tuesday #166, and another one back in 2018 or 2019 (I forget and I'm far too lazy to go look) have been on Extended Events. At conferences I'm frequently the only one doing sessions on Extended Events (although, sometimes, Erin Stellato is there, presenting a better session than mine). I did a session at SQL Konferenz in Germany earlier this week on Extended Events. Hanging out in the hallway at the event (which was great by the way), I was talking with some consultants. Here's their paraphrased (probably badly) story: "I was working with an organization just a few weeks back. They found that Trace was truncating the text on some queries they were trying to track. I asked…
When I was put on the list to host September's T-SQL Tuesday, well, I forgot to put it in my calendar. So I'm late (and in the doghouse with Steve). Because of this, I'm going to bend the rules a little (sorry Steve) and give you a few days to get your posts together. In theory, they're all due tomorrow, Tuesday, September 12. However, let's say they're all due by the end of the day on Thursday, September 14th. My apologies for being tardy. I'll still post a roundup on Friday. So, what's the topic for T-SQL Tuesday. Well, it's in the title, Extended Events. Let's talk about it. Why Extended Events? As anyone who has read my blog or books, or seen me speak, you'll know that I've got…
Wouldn't it be great to be able to directly monitor specific behaviors within SQL Server, like, oh, I don't know, knowing exactly when, and how, someone is using BULK INSERT? Well, you can, thanks to Extended Events through the bulk_insert_usage event. Bulk_insert_usage The BULK INSERT command is extremely useful within SQL Server. It's a way to move data into the database and provide some formatting on the way, efficiently, all through T-SQL. Hard to argue with the utility. Obviously, if you're doing traditional data collection through Trace or Extended Events, you'll see BULK INSERT commands within the T-SQL. However, Extended Events provides a specific event that tracks just the behavior of BULK INSERT: bulk_insert_usage. Documentation on this is somewhat sparse. Some of the best is from a standard source, Jason…
I hear this one all the time: How do I find out who implemented object changes? I also get: Can I see the query that caused object changes? Let's take a look at how you might audit who is doing what and how to your databases. Object Changes in Extended Events If you open up the New Session window for Extended Events in SSMS, the easy way to track down events is to simply type into the box. Here, we care about capturing object changes, so I'm going to simply type object, then scroll a bit: There we are object_altered, object_created and object_deleted. These are the same events that you would see in Trace. Let's use the GUI and take a quick look at what fields they capture: That's in…
One of my favorite little bits of information about Extended Events is the fact that everyone running a full instance of SQL Server has deadlock information available to them, even if they never enabled Trace Flag 1222 through the system_health session. That captures the xml_deadlock_report which has the full deadlock graph. However, what if you want to capture deadlock info, but, you're dealing the GDPR, and transmitting query values could be problematic? Enter xml_deadlock_report_filtered. xml_deadlock_report_filtered If you do a search for this event, you're not going to find much. Doesn't seem like anyone, including Microsoft, has bothered to document it. This is not going to be a comprehensive definition for all things xml_deadlock_report_filtered. However, I can show you why you might want to use it. This is a port of…
Most of the time when I talk about or demo Extended Events, I spend more time talking about query tuning (I have a problem). However, there are tons of things that you can do with Extended Events. Here's a little one that came up, auditing unique constraint violations. Unique Constraint Violations Whether we're talking a primary key or just a constraint, the error you get is number 2627 when you attempt to add a non-unique value. So, the code for a simple way to track this in Extended Events would look like this: CREATE EVENT SESSION [UniqueConstraintViolation] ON SERVER ADD EVENT sqlserver.error_reported (WHERE ([error_number] = (2627))); That's it. That's all you need. Probably, it'd be a good idea to output this to a file (that's usually what I do). However,…