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
10Feb 2020 by Grant Fritchey 7 Comments

Extended Events: Histogram Output

SQL Server, You Can't Do That In Profiler
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…
Read More