19Sep 2022 by Grant Fritchey No Comments

Extended Events for Anything but Query Tuning: bulk_insert_usage

SQL Server
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…
Read More
08Aug 2022 by Grant Fritchey No Comments

Extended Events for Anything but Query Tuning: Object Changes

SQL Server
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…
Read More
11Jul 2022 by Grant Fritchey No Comments

Extended Events for Anything But Query Tuning: xml_deadlock_report_filtered

SQL Server
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…
Read More
05Jul 2022 by Grant Fritchey 2 Comments

Extended Events for Anything But Query Tuning: Unique Constraint Violations

SQL Server
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,…
Read More